Assistance with counting cells with numbers

[Neil Grantham]

Hi, I have a problem when counting a column of numbers, in that the
count is not correct.

To explain, the count should return a number of matches in which a
person participated. I've used a SUMPRODUCT calculation to pick out
certain codes in another column. The hoped for result would return a
number of matches. There are 14 matches, and say he misses one it
should return 13, but it's not, it's returning 14. I want the
calculation to just count cells that have numbers in them (a blank is
when he didn't participate)

I'll explain better with the code perhaps.
So, the Cell calculating the number of attended mathes is this:
=SUMPRODUCT(($D3:$D52="H")*($E3:$E52="ELA")*(P352>0))+SUMPRODUCT(($D3:$D52="H")*($E3:$E52="ELB")*(P352>0))

I used ">0" thinking it would count values over 0. I also tried >""
which was wrong too.

Just to complete this, I am using the following calculation in column
P to keep it blank rather than have a 0
=IF(COUNTBLANK(I52)>0,"",(COUNTIF($I52:$O52,"3")+COUNTIF($I52:$O52,"2")+COUNTIF($I52:$O52,"1")+COUNTIF($I52:$O52,"0")+COUNTIF($I52:$O52,"R")+COUNTIF($I52:$O52,"Fx")+COUNTIF($I52:$O52,"1'")+COUNTIF($I52:$O52,"2'")+COUNTIF($I52:$O52,"F")))

Perhaps that's screwing it up?

Hopefully this makes sense to someone who can show me my error.

Many thanks
Neil

 

[Tom Ogilvy]

=SUMPRODUCT(($D3:$D52="H")*($E3:$E52="ELA")*isnumber(P352))+SUMPRODUCT(($D
3:$D52="H")*($E3:$E52="ELB")*isnumber(P352))

 

[Biff]

Hi Neil,

Change the criteria to .....*(P352<>""))

Since your second formula surpresses the zero value to
return the null string "", there shouldn't be any zeros in
column P. However, the null string evaluates to >0.

Biff