How do I stop Excel counting blanks as zeros?

[Fast Eddie]

Great site this, the knowledge people have is phenomenal. I've already searched for this question or something similar but couldn't find a solution. I have a sheet to analyze football(soccer) scores. But when I count the zero scores (no goals scored) across a range blank cells in the range are counted as zero, which I don't want. Is there any command I could put with the formula to tell it not to count blank cells as zero or just not to count blank cells at all? The sort of formula I use is {=COUNT(IF(H103:H559=0,(IF(I103:I559=0,1))))}

 

[sanrv1f]

try

=SUMPRODUCT(--ISNUMBER(H3:H559))

 

[Colin Legg]

Hi,

2 questions.....



Question 1

When you say "zero scores", are you trying to count the number of times no goals were scored by the teams, or are you trying to count the number of times a match was drawn 0 0 (ie. No goals were scored by both teams)?

So with this data:
1 0
0 0
0 0

Do you want a count of 2 or a count of 5?



Question 2

Which version of Excel are you using?

 

[mubashiraziz]

Try it ................. Suppose the data is in H2:I2 ........ it will count zero only when both teams didn't score a goal .......


=SUMPRODUCT(--(H2:H5=0)*(H2:H5<>""),--(I2:I5=0)*--(I2:I5<>""))

 

[sanrv1f]

sorry, I did't read the post correctly

if you wish to count 0's alone (excluding blanks), then

try

=SUMPRODUCT(--ISNUMBER(H3:H559),--(H3:H559=0))

or

can you post sample data with expected result?

 

[Fast Eddie]

Well, the formula in my first post is for counting 0-0 scorelines across a whole range and it works fine unless there are blank cells (where a game has been postponed or not played yet) which it then counts as a zero which of course messes up the count.

 

[fairwinds]

Hi,

Try:

=SUMPRODUCT(--(H103:H559&" "&I103:I559="0 0"))

 

[mubashiraziz]

It will work ..........

=SUMPRODUCT(--(H103:H559=0)*(H103:H559<>""),--(I103:I559=0)*--(I103:I559<>""))

 

[Fast Eddie]

Hi,

2 questions.....



Question 1

When you say "zero scores", are you trying to count the number of times no goals were scored by the teams, or are you trying to count the number of times a match was drawn 0 0 (ie. No goals were scored by both teams)?

So with this data:
1 0
0 0
0 0

Do you want a count of 2 or a count of 5?



Question 2

Which version of Excel are you using?

I would want a count of 2 there (nil all draws)

and I'm using excel 2002.

 

[mubashiraziz]

In Post 7 & 8 you have got the answer!!!!!!!!!!!!