Forum Discussion
Trying to figure out if I use CountIF, CountIFs with multiple criteria and logic.
I am trying to figure out if a region/section in the column is blank or not. It's in order by region but each region can be listed multiple times, so, for example, SEC36 is on the list 30 times, but only 14 of them have a plan listed. The plan is listed in Column E. I don't need to know which type of plan, I just need to know if they have one or not.
=COUNTIFS(B2:B206,"SU036", E2:E206,"<>")
I started with this, and it counts column B, but if it's blank or not.
PeterBartholomew1 , IMHO the main question here is what we consider as empty cell. That could be the blank cells (no content at all) only, or blank cells and cells with empty string ("", which we usually use to imitate blank cells). If do small test we will see the difference
Here two yellow cells are with empty string and one cell is blank. Total 6 cells, 2 empty plus 1 blank.
COUNTA() doesn't consider cells with empty strings as blank and count them, same do COUNTIFS(). Just in case, COUNTBLANK() in opposite counts both blank and empty ans blanks. SUMPRODUCT() does correct calculations excluding all cells with empty strings and blank ones. Variant with LEN() is more straightforward and gives less reasons for misinterpretations.
As for @ that's not wildcard, only the character which is more close to the beginning of ASCII table compare to other characters. However, # is even earlier plus any number is always less than any text, thus formula counts only the cell with a1.
Back to initial question I'd use SUMPRODUCT like
=SUMPRODUCT((B2:B206="SU036")*(LEN(E2:E206)>0))
16 Replies
- The criteria2 argument of COUNTIFS is incomplete. There is no value against which E2 could be compared.
Twifoo wrote:
The criteria2 argument of COUNTIFS is incomplete. There is no value against which E2 could be compared.A criterion "<>" in any of the IFS functions does carry meaning.
The equivalent condition in SUMPRODUCT, say, would be
= ( value <> "" )
Within an IFS function is suppose it could be presented as
= COUNTIFS( …, …, value, "<>" & "" )
but that reduces to the first form.
- No value as in a number? I just want it to count if it has text in the box? Maybe I need CountA?
The formula looks correct and should return the count of cells containing "SU036" with non-blank plans.
- That is what I thought it gives the number 14, but when I clear a plan the number does not change.
Armanda406 , how do you clean the plan, just delete the value from the cell(s) or by some other way? If that's the formula which returns "" your COUNTIFS won't consider it as the blank.
