Struggling with Simple Countif function

[smalik]

I have a column that looks up a value from a table and if the value does not match it results a blank cell.

I am trying to count how many cells have values

Both "Countif" and "Counta" formulas results in the same answer because even if the cell appears blank, it has a formula. How can I count non blank cells in this situation?

Sorry, I cannot use the "addin" to my work computer. Hopefully, the picture does the trick. The yellow cells in this example show a blank cell, but they have a lookup formula.

Column "D" shows the formula I am using to count.

Any help is greatly appreciated.

Thanks in advance.

 

[Trebor76]

Hi smalik,

As B6 and B7 have formulas that even though they are returning blanks they are not empty hence the issue.

Try this:

=COUNTA(B3:B9)-COUNTBLANK(B3:B9)

Regards,

Robert

 

[HighAndWilder]

Does =SUMPRODUCT(--(LEN($B$3:$B$9)>0)) work for you?

 

[jtakw]

Hi,

Also try:

=SUMPRODUCT(--(B3:B9<>""))

and

=SUMPRODUCT((B3:B9<>"")*1)

 

[Peter_SSs]

Or try

=COUNTIF(B3:B9,"?*")

 

[smalik]

Thank you everyone. All options worked.
@Trebor76, @HighAndWilder, @jtakw, @Peter_SSs

Much appreciated your help.

Best,

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.

 

[jtakw]

You're welcome, thanks for the feedback.