Counting blanks, either 1, 2 or 3

[Guest]

I'm counting blank cells with this formula:
=COUNTIF(Y3:Y223,"")

In column X, I have this formula =LEFT(S74,1), which results in either a
1, 2 or 3

How could I count the blank cells that correspond with the 1's, the 2's &
the 3's ?

Thanks,

Steve

 

[John Michl]

Steve -
You can use the SUMPRODUCT function to do a multi-criteria count. The
trick is when you use the LEFT function, the result is a text string so
you must convert the numerical criteria to text.
Assuming original "numbers" are in column S, the resulting leading
numbers (as text) are in column X and the other criteria in Y....

=SUMPRODUCT(--(X1:X100="1"),--(ISBLANK(Y1:Y100))

To avoid the "1" business, change your formula in column X to
=VALUE(LEFT(S74,1)) to create a number value instead of a text value.

You might consider this variation to eliminate the need for column X...

=SUMPRODUCT(--(LEFT(S1:S100,1)="1"),--(ISBLANK(Y1:Y100)))


- John Michl

 

[Biff]

Hi!

Try this:

=SUMPRODUCT(--(A1:A10=""),--(ISNUMBER(MATCH(B1:B10,{1,2,3},0))))

Biff

 

[Biff]

Ooops!

I forgot about the LEFT function returning TEXT as John noted.

You can either use the Sumproduct formula as is and change your LEFT formula
to:

=LEFT(cell_ref)*1

Or

=--LEFT(cell_ref)

These will convert the TEXT numbers to NUMERIC numbers.

Or, just change the Sumproduct formula by enclosing the array constants in
quotes:

=SUMPRODUCT(--(A1:A10=""),--(ISNUMBER(MATCH(B1:B10,{"1","2","3"},0))))

In general, it's not a good idea to have formulas returning TEXT numbers.

Biff

 

[Guest]

Thanks guys,

Everything worked very nicely. Much appreciated.

Thanks again,

Steve

 

[Guest]

Hi,

You may want to try the following array formula (Ctrl+Shift+Enter):

=count(if(((range=1)+(range=2)+(range=3)*(range2="")),range2))

Regards,

Ashish Mathur