sumproduct counts empty cells as well a 0

[baseball]

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

[Juan Pablo González]

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.

[Juan Pablo González]

And, you could use an ugly formula like this

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

which returns 1.

[baseball]

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

[Aladin Akyurek]

...
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 ]

[baseball]

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