Don't COUNTIF if adjacent cell is blank

[Itsmeerc]

Hi there,

I am currently working with a fairly simple COUNTIF formula in the attached image. The count in column L is based on =COUNTIF(G:G, K8) and so on.

The problem I have is that the ward list in column K is drawn from a separate sheet in the workbook, using ='Select LA'!C5 and so on, and updates based on this, getting longer or shorter. I therefore want to copy the =COUNTIF formula in column L down so that it will continue to provide a count when the ward list gets longer. However, using this formula when the adjacent cell is blank gives the value 1042652.

So, my question is, what formula can I use in column L that will count column G, based on column K, but will not return a value if the adjacent cell in column K is blank.
Thanks.

 

[AlanY]

will this work for you?


=if(isblank(K8),"",COUNTIF(G:G, K8))

 

[jasonb75]

That should be

=IF(K8="","",COUNTIF(G:G,K8))

ISBLANK doesn't work with formula blanks, only empty cells.

 

[Itsmeerc]

will this work for you?


=if(isblank(K8),"",COUNTIF(G:G, K8))

Thanks for the reply but, as Jason said, same issue persists with this. I perhaps should have made it clearer that the cells in K are not empty but contain the SELECT formula.

 

[Itsmeerc]

That should be

=IF(K8="","",COUNTIF(G:G,K8))

ISBLANK doesn't work with formula blanks, only empty cells.

Thank you for this - in my earlier reply I somehow didn't notice the top half of your response, but this worked perfectly. Much appreciated!