count how many tims a certain value occurs

[lucnijs]

Hi!

I have a spreadsheet for the amount of times students practised their instrument and for the help they get from their parents

so I have 2 columns per day, one for the amount of time practised and one to indicate if and by whom they were helped.
e.g.
F = Monday_practiceTime
G = Monday_helpedBy
H = Tuesday-practiceTime
I = Tuesday_helpedBy
J = Wednesday_practiceTime
K = Wednesday_helpedBy
...

I now want to see how many days they practised a week.
See I was thinking to count the cells with a number greater than 0.
I used COUNTIF:

COUNTIF(F2+H2+J2+L2+N2+P2+R2, ">0")

But I get an error message saying that the range is invalid.

How can I do this right?


I use Excel for mac 2008

Thank you!

Luc

[Pecoflyer]

Perhaps

=SUMPRODUCT(--(MOD(COLUMN(F2:R2),2)=0),--(F2:R2>0))

[lucnijs]

thank you for your answer.
I've tried but I still get an error message...

[Pecoflyer]

Originally Posted by lucnijs

thank you for your answer.
I've tried but I still get an error message...

Could you please tell waht the error message was?
Is it related to the fact you are using XL for MAC ?

[Colin Legg]

I believe the COUNTIFS function is available in Excel 2008, so something like this should work:

=COUNTIFS(F1:T1,"*practiceTime",F2:T2,">0")


We're applying two conditions with this formula. The first condition is that the cell in the header row F1:T1 must contain the word practiceTime. The second condition is that the cells in the data row F2:T2 must contain a value greater than 0.

[lucnijs]

Thank you, Colin!
Unfortunately I get an error message again, saying that criteria_range1 is invalid.

luc

[Colin Legg]

Unfortunately I get an error message again, saying that criteria_range1 is invalid.

Did you make any changes to the formula I posted? I've attached example which uses it.