COUNT cells, skip empty and with formula that results to ""

0
votes

I have formulas in range H13:O13 that are giving ="" as a result. For example =IF(A1="";"";A1) in B1. I would like to count number of values that are not ="".

Some cells are merged in H13:O13.

I have tried:

=COUNTIF(H13:O13;"")

COUNTIF works but back wise. It is like amount of cells - used ones

also

=COUNT(H13:O13)

COUNT does not recognize ="". If cell result is ="" it counts it as well.

EDIT:

Suggested formula:

=COUNT(H13:O13)-COUNTIF(H13:O13;"")

enter image description here

Result is -2, it should be 3 as there are 3 values in range and one is empty. I know they are merged but that is what I am expecting from formula

1
excel-formula
and what happened when you tried those, and why was that wrong? – underscore_d
COUNTA counts used cells, but it will count cells that return "". COUNTIF(B1:B50;"<>") does the same. But COUNTIF(B1:B50;"") correctly counts empties, so your best bet is COUNT(B1:B50) - COUNTIF(B1:B50;""). – GSerg
Well I would like to know amount of used cells. This formula is giving me -8 – hatman

1 Answers

1
votes

try:

=ROWS(B1:B50)-COUNTIF(B1:B50,"")

it will not count empty cells or cells containing stuff like =""

TRICK:

If you have a block of cells like A1 through E5, then:

=ROWS(A1:E3)*COLUMNS(A1:E3)-COUNTIF(A1:E3,"")

enter image description here