Forum Discussion
Trying to figure out if I use CountIF, CountIFs with multiple criteria and logic.
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))
The formula looks correct and should return the count of cells containing "SU036" with non-blank plans.
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.
- Yes, I was just deleting the value from the cell. I was just trying to test it to see if it would update, but I remember now that it will still count it, so if I don't need to clear a plan then it is working. My other part to this, is how do I get the formula to count each change in SU?
Hi,
Do I understand correctly what other words you need number of unique SU minus one? Or that could be the situation when you change from SU1 on SU2 and after that back on SU1 and it will be 2 changes?
What do you think of ">@" as a test for the existence of text?
It attempts to combine Armanda406's idea of COUNTA with the IFS formula structure.
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))