How to do count with if

[no-1]

Hai,

Need a sugestion from you all. I have problem to count my data. For example,
I want to count for each Team how many coaching or counseling had been done.

ColumnA .....ColumnC
TEAM Action
Level 1 Coaching
Level 1 Coaching
Level 2 Caunseling
Level 3 Counseling
Level 2 Coaching
Level 3 Coaching
Level 1 Counseling

I tried using this formula, but it giving me all result as 1

=COUNT(IF((A2:A11="Level 1")*(C2:C11="Coaching"),C2:C11))

And when I tried this formula, the result will be 0

=SUM(IF(A2:A11="Level 1",IF(C2:C11="Coaching",1,0)))

Please help.

 

[Roger Govier]

Hi

Try
=SUMPRODUCT((A2:A11="Level 1")*(C2:C11="Coaching"))
Instead of using "level1" or "Coaching", you could put those values in say
cells D1 and E1 and use
=SUMPRODUCT((A2:A11=D1)*(C2:C11=E1))

Changing the values in D1 and E1 will give you results for different queries
without changing the formula.

It works because each test produces a result of True or False.
Multiplying them together coerces True to 1 and False to 0 so you end up
with and array of
1 x 1 = 1
1 x 1 = 1
0 x 0 = 0
0 x 0 = 0
0 x 0 = 0
0 x 0 = 0
1 x 0 = 0

which Sumproduct then totals to give a result of 2

 

[no-1]

Dear Roger,

The formula did help me.

Thank you very much! =)