Formulas returning blank being read as >0 Why?

[Bonkers Bill]

Hello, i am using the following formula in excel 2007

=IF($O$3="HOME",IF(INDEX(AC5:DV5,MATCH($R$4,$AB$3:$DU$3,0))="","",IF(INDEX(AC5:DV5,MATCH($R$4,$AC$3:$DV$3,0))="L","",INDEX(AC5:DV5,MATCH($R$4,$AB$3:$DU$3,0)))),"")

when this formula returns "" blank and i copy and use paste special, values only, another formula is reading the "" blanks as >0, Why? and what can i do to stop this?

 

[Bonkers Bill]

Hello, i'm trying to alter the formula above usining N to countif cells contain numbers including 0's
=COUNTIFS(Scores!$A$4:$R$120,C7,Scores!$B$4:$S$120,"(N)")

This works but doesn't count 0's
=COUNTIFS(Scores!$A$4:$R$120,C7,Scores!$B$4:$S$120,">0")

Any ideas welcome, cheers

 

[Aladin Akyurek]

Hello, i'm trying to alter the formula above usining N to countif cells contain numbers including 0's
=COUNTIFS(Scores!$A$4:$R$120,C7,Scores!$B$4:$S$120,"(N)")

This works but doesn't count 0's
=COUNTIFS(Scores!$A$4:$R$120,C7,Scores!$B$4:$S$120,">0")

Any ideas welcome, cheers

What do you have in C7?

Are you trying to count numbers greater than 0, which occur in the relevant ranges as part of Scores!$B$4:$S$120? If you want to count the real 0's too, you need ">=0" instead of just ">0".

 

[Bonkers Bill]

Hi Aladin, c7 is the name of the team i am counting the amount of games played, unfortunately they have a score of 0-9 and the formula wont count the 0 as there are lots of blank cells for future matches not played yet, i entered ">=0" but it has returned me 19 instead of 8 with a possible 28 matches

 

[Aladin Akyurek]

Hi Aladin, c7 is the name of the team i am counting the amount of games played, unfortunately they have a score of 0-9 and the formula wont count the 0 as there are lots of blank cells for future matches not played yet, i entered ">=0" but it has returned me 19 instead of 8 with a possible 28 matches

Let's check something:

What do we get with:

=COUNTIF(Scores!$B$4:$B$120,">=0")

and with:

=SUMPRODUCT(--(ISNUMBER(Scores!$B$4:$B$120),--(Scores!$B$4:$B$120>=0))

 

[Bonkers Bill]

The 1st returns 21, the 2nd formula contains an error
niether formula covers the range required

 

[Aladin Akyurek]

The 1st returns 21, the 2nd formula contains an error
niether formula covers the range required

Darn parens...

=SUMPRODUCT(--ISNUMBER(Scores!$B$4:$B$120),--(Scores!$B$4:$B$120>=0))

Bill: I'm not trying to cover the whole range. Just carrying a test...

Thus:

=COUNTIF(Scores!$B$4:$B$120,">=0")

What does yield the corrected SumProduct?

returns 21

 

[Bonkers Bill]

the countif returns 21 which is the correct answer

 

[Bonkers Bill]

the sumproduct also returns 21

 

[T. Valko]

the sumproduct also returns 21

Is there any way you can post your file so we can see what you're trying to do?

You can use a free file host if needed.

 

[Bonkers Bill]

i'm not sure how to do that as it is over 3 work sheets
the sumproduct formula does count the 0's as well if i add one in so i'm sure this will work if modified