Forum Discussion
Countif problem with testing for non-blank cells
I've created a COUNTIF function to summarise data from a table, where one of the criteria I want to use is whether a particular cell is empty. Ideally, I wanted to use the ISBLANK function but I could not work out how to include it in my COUNTIF, so followed some advice from the internet to test for "*" instead.
The formula I'm using is:
=COUNTIFS(_GC_Status,Summary!$A5,_GC_Index,Summary!D$4&"*",_GC_Closed_date,"*")
where _GC_Status, _GC_Index and _GC_Closed_date are ranges in my data table, and Summary!$A5 and Summary!D$4 are row and column labels that correspond to text entries in the data table.
The problem is with the third criteria - this seems to count correctly when the cell contains a text entry but skips when it contains a date entry.
Ideas on how to fix this would be much appreciated.
Now it is counting the cells with dates in but skipping the ones with text, so the opposite of my previous problem.
- Try using a sum of two COUNTIFs: first is your one and second is the same with ">0" instead of "*". The 1st COUNT counts text cells and the 2nd COUNT adds date cells. This works for me.
