How do I stop Excel counting blanks as zeros?

Fast Eddie

New Member
Joined
Nov 7, 2008
Messages
5
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))))}
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

sanrv1f

MrExcel MVP
Joined
Jan 1, 2009
Messages
3,474
Office Version
  1. 2016
Platform
  1. Windows
try

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

Colin Legg

MrExcel MVP
Joined
Feb 28, 2008
Messages
3,497
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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?
 
Last edited:

mubashiraziz

Board Regular
Joined
Apr 2, 2009
Messages
175
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

MrExcel MVP
Joined
Jan 1, 2009
Messages
3,474
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Nov 7, 2008
Messages
5
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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638

ADVERTISEMENT

Hi,

Try:

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

mubashiraziz

Board Regular
Joined
Apr 2, 2009
Messages
175
It will work .......... :)

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

Fast Eddie

New Member
Joined
Nov 7, 2008
Messages
5
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,477
Messages
5,601,893
Members
414,479
Latest member
Beau the dog

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top