Mysterious Excel Formula Syntax to Count Text Cells (as NEVER Seen Before)
5 March 2020 Excel Hacks: Tips & Tricks
It's safe to say this Excel formula syntax has NEVER been seen before. My team and I, did an extensive search on this mysterious Excel formula syntax, but found no information about it. It's a super short formula in Excel to count cells with text and ignore blank cells, numbers and also formula cells that result in blank cells or empty strings.
This is one of a kind Excel formula hack.
I received this special syntax for the Excel COUNTIF function from Jack, one of our community members. It works perfectly although I have no idea why. Because it looks like we are applying a wrong Excel syntax (angry eyes). If we were to write the formula "correctly" it would only exclude the truly blank cells. But cells that include formulas which result in blank cells would still be included in the count.
Alternatively, we can use the Excel COUNTIF function with the placeholders ? and *. This Excel count formula which uses wildcards, will also also just count non blank cells and ignore formulas that result in blank cells as well as ignore zeros and other numbers.
In a situation when we want to count all cells that include either text or numbers we can use SUMPRODUCT in combination with the LEN function. This way we can check if the length of the content in the cells is at least 1 character. You can replace SUMPRODUCT with SUM if you have the new Dynamic Array Excel - if you have legacy Excel and use SUM instead of SUMPRODUCT you need to press Control+shift+enter.
As a summary - In this Excel tutorial we take a look at different methods to Count in Excel:
1. Excel formula hack to count text cells: 2:07
2. Alternate Excel formula to count text cells (with wildcards): 2:52
3. Count all cells except the truly blank cells in Excel: 3:49
4. Count all text cells and numbers in Excel and exclude blank cells and formulas that result in blank cells: 4:30
⯆ DOWNLOAD the workbook here: https://www.xelplus.com/excel-formula-hack-count-text
Excel hacks and tricks playlist: https://www.youtube.com/playlist?list=PLmHVyfmcRKyxVeel8DFjewBSlZYdfd38A
★ My Online Excel Courses ► https://www.xelplus.com/courses/
✉ Not sure which of my Excel courses fits best for you? Take the quiz: https://www.xelplus.com/course-quiz/
EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/
Get Office 365: https://microsoft.msafflnk.net/15OEg
Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593
GEAR
Camera: https://amzn.to/2FLiFho
Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161
Microphone: https://amzn.to/2DVKstA
Lights: http://amzn.to/2eJKg1U
More resources on my Amazon page: https://www.amazon.com/shop/leilagharani
Let’s connect on social:
Instagram: https://www.instagram.com/lgharani
Twitter: https://twitter.com/leilagharani
LinkedIn: https://at.linkedin.com/in/leilagharani
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#MsExcel #Excel #LeilaGharani
-
LIVE
Mostly Sports With Mark Titus and Brandon Walker
3 hours agoMostly Sports With Mark Titus and Brandon Walker Presented by Jägermeister | EP 186 | 6.11.24
698 watching -
1:08:38
Graham Allen
3 hours ago🚨BUSTED: Pelosi ADMITS Jan 6th Was HER FAULT!! + Hunter Deliberations Have Begun
26.9K209 -
LIVE
Matt Kohrs
10 hours agoHunter Biden Faces Prison, Meme Stocks Crushed & Markets Fall || The MK Show
1,048 watching -
2:03:31
The Kirk Minihane Show
3 hours agoKMS Live - June 11, 2024
5.63K1 -
40:21
Rethinking the Dollar
1 hour agoThe Dollar Takes Center Stage: All Eyes on U.S. Banks & the BRICS | Tuesday Morning Check-In w/ Mike
562 -
1:28:16
Game On!
11 hours agoOur 1 Year Anniversary Stream! Momma We Made It! | Sports Morning Espresso Shot
15K5 -
1:32:53
jeffahern
2 hours agoTrending Tuesday With Jeff Ahern
13.4K3 -
16:52
NC Dirt Hunter
1 day agoCivil War secrets revealed: Saving lost relics from the Battlefield with a Metal Detector!
20.8K4 -
23:15
ScammerRevolts
18 hours agoScammers Still Had Access To His Pc... So I Removed Them
24.7K7 -
2:44
Michael Heaver
23 hours agoLe Pen French Victory Sparks Shock ANNOUNCEMENT
29.4K19