Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel formulae | New Users to Excel | |||
Excel Formulae 'Help' | Excel Worksheet Functions | |||
Excel Formulae | Excel Worksheet Functions | |||
Formulae: Paste value formulae after doing an average operation | Excel Discussion (Misc queries) | |||
Searching TEXT in formulae, rather than results of formulae | Excel Worksheet Functions |