New to Excel Formulae
 

Dear All,

I am trying to create a formula that uses several columns to generate
totals e.g.

Mon Pub 1
Wed Club 1
Thu Gym 1
Fri Pub 1
Mon Gym 1
Tue Club 1
Thu Gym 1
Fri Club 1
Mon Gym 1

Given the above table, I have used the SUMIF formula to tell me how
many times the person went to the Gym [=SUMIF(B1:B9,"Gym",C1:C9)], but
now I want to know if I can take this one step further to tell me how
many times the person went to the Gym on a Thu?

I've also thought about using IF statements, but the only way that I
can see of doing this is by having an IF statement (possibly several
within one) for each entry. This is not good for me though as Ideally
I need to have one formula for each permutation (see below).

Gym Pub Club
Mon 2 1
Tue 1
Wed 1
Thu 2
Fri 1 1

NOTE: This is just an example, and the real data is a lot larger in
quantity.

Thank you for any help that you can give me,
Debs.

New to Excel Formulae
 

=SumProduct((A1:A9="Mon")*(B1:B9="Gym"))

will get a count of the rows containing Mon and Gym

If the numbers in the third column could be other than 1
=SumProduct((A1:A9="Mon")*(B1:B9="Gym")*(C1:C9))

Will sum up the third column for rows containing both Mon and Gym.

--
Regards,
Tom Ogilvy


"Debs Wisbey" wrote in message
om...
Dear All,

I am trying to create a formula that uses several columns to generate
totals e.g.

Mon Pub 1
Wed Club 1
Thu Gym 1
Fri Pub 1
Mon Gym 1
Tue Club 1
Thu Gym 1
Fri Club 1
Mon Gym 1

Given the above table, I have used the SUMIF formula to tell me how
many times the person went to the Gym [=SUMIF(B1:B9,"Gym",C1:C9)], but
now I want to know if I can take this one step further to tell me how
many times the person went to the Gym on a Thu?

I've also thought about using IF statements, but the only way that I
can see of doing this is by having an IF statement (possibly several
within one) for each entry. This is not good for me though as Ideally
I need to have one formula for each permutation (see below).

Gym Pub Club
Mon 2 1
Tue 1
Wed 1
Thu 2
Fri 1 1

NOTE: This is just an example, and the real data is a lot larger in
quantity.

Thank you for any help that you can give me,
Debs.