# sumproduct counts empty cells as well a 0

This is a discussion on sumproduct counts empty cells as well a 0 within the Excel Questions forums, part of the Question Forums category; 8th 9th 10th 11th 12th Team 1 0 3 0 1 BOS 1 2 0 BOS 4 0 0 2 ...

1.  8th 9th 10th 11th 12th Team 1 0 3 0 1 BOS 1 2 0 BOS 4 0 0 2 Opp 0 0 0 1 BOS

I have data in an array like this (B2:E5), and I want to count, say, all the times that a zero (0) occurs in the column called 11th. However, when using =SUMPRODUCT((D2:D5=0)*(F2:F5="BOS")), I get an answer of 2 instead of 1 because the formula is counting the empty cell, C3. Similarly, I get the value of 2 for the column 12th.

What do I do to avoid counting empty cells using a sumproduct formula? I could place a filler like "---" in the empty cells, but there are far, far too many to do this by hand. Is there some way to do this automatically without overwriting my data?

Thanks,

Cliff

2. To fill in empty cells, you can use the Go to, special, blank cells only, which would select all the blank cells in a specific range (If you selected a range before) or the entire sheet.

Then, you could just type

-

and press Control Enter to fill all the selected cells.

3. And, you could use an ugly formula like this

=SUMPRODUCT((LEN(D2:D5)>0)*(D2:D5=0)*(F2:F5="Bos"))

which returns 1.

4. Thanks. I had forgotten about Special/Blanks only. Since this spreadsheet is getting computionally huge, I'll probably use that instead of the formula you suggested; althought, that is a use of the LEN function I had never considered.

Cliff

5. ...
I have data in an array like this (B2:E5), and I want to count, say, all the times that a zero (0) occurs in the column called 11th. However, when using =SUMPRODUCT((D2:D5=0)*(F2:F5="BOS")), I get an answer of 2 instead of 1 because the formula is counting the empty cell, C3. Similarly, I get the value of 2 for the column 12th.

What do I do to avoid counting empty cells using a sumproduct formula? I could place a filler like "---" in the empty cells, but there are far, far too many to do this by hand. Is there some way to do this automatically without overwriting my data?

Thanks,
Cliff
=SUMPRODUCT((ISNUMBER(D2:D5))*(D2:D5=0)*(F2:F5="BOS"))

would work, since an empty cell does not house a number.

[ This Message was edited by: Aladin Akyurek on 2002-08-12 12:31 ]

6. I thought about the ISNUMBER function, but I couldn't figure out how to tie it in with the SUMPRODUCT. I kept thinking about IF statements.

Thanks,
Cliff

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•