countif cell is not blank

[mira2020]

I use below formula to count non blank cell
=COUNTIF(BG5:BG6,"<>")

have formula on these cell, sometimes the formula return blank but my countif always count the cell because the formula is there

How to resolve it?

thanks,

 

[eiloken]

I use below formula to count non blank cell
=COUNTIF(BG5:BG6,"<>")

have formula on these cell, sometimes the formula return blank but my countif always count the cell because the formula is there

How to resolve it?

thanks,

just use COUNTA bro

 

[SanjayGMusafir]

=COUNTIF(BG5:BG6,"<>")

=COUNTIF(BG5:BG6,"<>")-COUNTIF(BG5:BG6,"")

Better

=COUNTA(BG5:BG6)-COUNTIF(BG5:BG6,"")

 

[Phuoc]

Try this:

=COUNTIF(BG5:BG6,"?*")+COUNT(BG5:BG6)

Or:

=SUMPRODUCT(--(LEN(BG5:BG6)>0))

 

[Alex Blakenburg]

SumProduct and if you have MS 365 Filter seem to handle this the best, so on Excel 2016 try.

=SUMPRODUCT(--(BG5:BG6<>""))

@SanjayGMusafir - in your formula the "<>" is only excluding Empty/Blank Cells but the subtraction criteria "" is including both empty string ie "" AND empty/blank cells.

 

[Alex Blakenburg]

Try this:

=COUNTIF(BG5:BG6,"?*")+COUNT(BG5:BG6)

The downside of "?*" is that it will only count cells containing text. It will ignore numbers.