conditional formula

[Lindie]

I had had lots of help creating a spreadsheet- may thanks guys. One bit remains a mystery to me.
I'd like to count data in one column but only if the entries in that column are not 0 -zero. Pupils are on different courses as indicated in the last column
How do I do that? Sample sheet is attached.
Many thanks
Lindie

[Hoopsah]

Hi,

try:


=COUNTIF(H8:H15,"<>0")


Cheers

Hoopsah

[Lindie]

A neat way indeed; however, my last column has either M,L or C. and it is these I need to count, not the frequency of column H. So if there is <>0 in column H then if a pupil is doing M (maths) the count needs to be 1- for M.
L

[Bob Phillips]

In cell D17

=SUMPRODUCT(--(D$8:D$15<>0),--($J$8:$J$15=$C17))

copy down and across

[Hoopsah]

Hi Lindie,

I'm getting a tad confused now.

If you want to count the M, L's or C's you could use:
=COUNTIF(J8:J15,"=M")

But I think you want more than this, I'm just not sure what.

[Lindie]

Great, =SUMPRODUCT(--(D$8:D$15<>0),--($J$8:$J$15=$C17)) works a treat xld. I did read your post on sumproduct and followed a link you suggested. I thought sumproduct might be a way to do it but simply could not figgure out how. I'll go back to that link and try and understand how it works. The "--" threw me. You are the king/Lord of arrays. Thanks ever so much
Lindie

[Bob Phillips]

Great, =SUMPRODUCT(--(D$8:D$15<>0),--($J$8:$J$15=$C17)) works a treat xld. I did read your post on sumproduct and followed a link you suggested. I thought sumproduct might be a way to do it but simply could not figgure out how. I'll go back to that link and try and understand how it works. The "--" threw me. You are the king/Lord of arrays. Thanks ever so much
Lindie

Yeah, that link takes some effort, but it is a great paper :devil2:

[Lindie]

Yeah, that link takes some effort, but it is a great paper :devil2:
Legum servi sumus ut liberi esse possimus

Or: understanding VBA gives us the freedom to do stuff with Excel

[Bob Phillips]

Legum servi sumus ut liberi esse possimus

Or: understanding VBA gives us the freedom to do stuff with Excel

Now I am confused, that paper doesn't even touch VBA, it is pure Excel.

[Hoopsah]

On the ball again Bob.

[Lindie]

Now I am confused, that paper doesn't even touch VBA, it is pure Excel.

Just shows you what a blonde I am....!
Lindie