You can also try:
=COUNTA(FILTER(B:G,A:A="Smith"))
I’ve been using COUNT for years without realizing it quietly skips text cells — only counts numbers!
So if your dataset has words like “Yes” or “N/A”, you’ll need =COUNTA() instead, which counts all non-empty cells.
COUNT → counts only numeric cells
COUNTA → counts everything that’s not blank
It’s a tiny detail but super important if you’re summarizing survey data or attendance sheets.
I made a short 30-sec clip showing it in action here if anyone wants to see the difference visually 👇
🎥 https://www.youtube.com/shorts/pd_9ng_7EAQ
What’s another Excel formula you think people commonly misunderstand? I’m thinking of doing a mini-series on these small-but-powerful differences.
If you like bite-sized Excel tips, I’ve been collecting all of them here:
https://www.youtube.com/playlist?list=PL5w9hG_JDbyjTCBFAdRVobtQVZD1PvQRt
google sheets - COUNTIF OR COUNTA - Stack Overflow
google sheets - How to use counta function with two columns (or) - Web Applications Stack Exchange
Google sheet formula- How do I get this to count correctly?
Why is this Google Sheets formula counting blank cells? - Web Applications Stack Exchange
You can also try:
=COUNTA(FILTER(B:G,A:A="Smith"))
The COUNTA Function is categorized under Excel Statistical functions. It will calculate the number of cells that are not blank within a given set of values. The COUNTA function is also commonly referred to as the Excel COUNTIF Not Blank formula.
The COUNTA function is useful if we wish to keep a count of cells in a given range. Apart from crunching numbers, we often need to count cells with values. In such a scenario, the function can be useful.
Please use
=QUERY(INDEX(A1:A&B1:B),"select count(Col1)",0)
Try this:
=COUNTA(IFERROR(FILTER(ROW(A:A),A:A&B:B<>"")))
ROW(A:A) gives us "something" to count.
By concatenating each row-cell of A:A with the corresponding row-cell of B:B, we form one string, which we can evaluate as one string and thus count as one string.
Your going to need a custom formula that can handle the various cases you’ve mentioned. A custom script would be ideal for this, but since we’re working within the constraints of Excel formulas, we might be able to use a combination of SUMPRODUCT and LEN - SUBSTITUTE to count the occurrences of each delimiter that separates the items, now this would only really work on the principle that the number of items is one more than the number of delimiters if the items are seperated.
So lets assume that the delimiter is a comma this is how I would do the formula: =SUMPRODUCT((LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5, “,”, “”)))/LEN(“,”)+1)
So this calculated the number of commas in each cell and adds 1 because there’s always one more item than there are delimiters but this means your data must be separated by delimiters. And in your case items are separated by a comma and a space, so we need to adjust the formula: =SUMPRODUCT((LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5, ", “, “”)))/LEN(”, ")+1)
If your items within the cells are not consistently separated by a specific delimiter, you would need a more complex formula or a script to accurately count the items.
Hello everyone, below is a picture of a spread sheet that I need to keep a total of the items listed. This is a small portion of the actual spreadsheet that is for tracking purposes. The items are not the word book but anything with 2 letters or 2 letters and a number, generally after the ||.
The book formula worked well because I counted every cell w/the word book as 1 item. The issue I run into and need to find a fix for is anything additional in the cell, such as A2 it lists 4 items not just 1 as the other cells show. Each ‘item’ is either S#, or DP, DP# or AP. Each item will always be either a letter and a number (A# or two letters and a number SP4 or a word or 3 letters AAA ), which is why i started to just count the word book, as i knew the word book would always be included in the cells no matter what the item was. Now i need to account for more than one item in a cell automatically which is the issue I am having trouble figuring out.
I need to count each item but have a way for the formula to include anything ‘extra’ in the cell as it shows in A2.
The original formula was ="Total of Books " &COUNTIF(A1:A5, “book”) which worked well until i realized I need to be inclusive of other items listed on the same sell so those are also counted.
I have used a few formulas shown below but those don’t work either.
="Total of Books " &COUNTIF(A1:A5, “book”) +countif(A1:A5, “s2”)+countif(A1:A5, “ap)”)
="Total of Books " &arrayformula (sum(COUNTIF(A1:A5, “book”) +countif(A1:A5, “s2”)+countif(A1:A5, “ap)”)))
The forumula below subtracts 1 from the total which doesn’t help if for whatever reason that time there are no extra items in a cell (if column just had 1 item)
="Total of Books " &arrayformula (sum(COUNTIF(A1:A5, “book”) +countif(A1:A5, “s2”)+countif(A1:A5, “ap)”),-1))
Unfortunately I cannot just have a2 has 4 separate instances which is why I am trying to figure out how to have the formula include any 'extras w/in the cell.
The total number of books based off the picture should be 7. One item for a1,a3, and a4 and a2 has 4 items.
Any help is appreciated.