Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use sumproduct for this?
is there a way i can use sumproduct or some other formula to data from the
following table for multiple criteria: Associate Month Quest 1 Quest 2 Quest 3 A jan 10 10 10 B jan 9 9 9 B feb 8 8 8 B mar 9 8 8 C jan 7 7 6 C feb 10 10 10 C feb 10 10 10 specifically what i need to do is count the # of times associate C has entries for Feb and return the totl # of all questions and the total of all questions sumed up. The answer for associate C for Feb would be 6 questions for a total of 30. i know this will require 2 different formulas in 2 separate cells, but that is ok. -- joec |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use sumproduct for this?
To me, it looks like 6 questions totaling 60, not 30!
If the months are text entries, try this: =Sumproduct((A2:A8="C")*(B2:B8="feb")*C2:E8) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "joec" wrote in message ... is there a way i can use sumproduct or some other formula to data from the following table for multiple criteria: Associate Month Quest 1 Quest 2 Quest 3 A jan 10 10 10 B jan 9 9 9 B feb 8 8 8 B mar 9 8 8 C jan 7 7 6 C feb 10 10 10 C feb 10 10 10 specifically what i need to do is count the # of times associate C has entries for Feb and return the totl # of all questions and the total of all questions sumed up. The answer for associate C for Feb would be 6 questions for a total of 30. i know this will require 2 different formulas in 2 separate cells, but that is ok. -- joec |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use sumproduct for this?
This will give you the count of questions (6):
=SUMPRODUCT((A2:A8="C")*(B2:B8="Feb")*((C2:C8<0)+ (D2:D8<0)+ (E2:E8<0))) Not sure why you think the total should be 30 - is it not 60? This one gives an answer of 60: =SUMPRODUCT((A2:A8="C")*(B2:B8="Feb")*(C2:C8+D2:D8 +E2:E8)) Hope this helps. Pete On Feb 21, 11:09*pm, joec wrote: is there a way i can use sumproduct or some other formula to data from the following table for multiple criteria: * * * * Associate * * * Month * * * *Quest 1 * * Quest 2 * *Quest 3 * * * * * * * A * * * * * * * jan * * * * * * 10 * * * * * * 10 * * * * * *10 * * * * * * * B * * * * * * * jan * * * * * * * 9 * * * * * * * 9 * * * * * * * 9 * * * * * * * B * * * * * * * feb * * * * * * * 8 * * * * * * * 8 * * * * * * * 8 * * * * * * * * B * * * * * * * mar * * * * * * 9 * * * * * * * *8 * * * * * * *8 * * * * * * * C * * * * * * * jan * * * * * * * 7 * * * * * * * 7 * * * * * * * 6 * * * * * * * C * * * * * * * feb * * * * * * *10 * * * * * * 10 * * * * * *10 * * * * * * * C * * * * * * * feb * * * * * * *10 * * * * * * 10 * * * * * * 10 specifically what i need to do is *count the # of times associate C has entries for Feb and return the totl # of all questions and the total of all questions sumed up. The answer for associate C for Feb would be 6 questions for a total of 30. i know this will require 2 different formulas in 2 separate cells, but that is ok. -- joec |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use sumproduct for this?
works great thanks!!!
-- joec "Pete_UK" wrote: This will give you the count of questions (6): =SUMPRODUCT((A2:A8="C")*(B2:B8="Feb")*((C2:C8<0)+ (D2:D8<0)+ (E2:E8<0))) Not sure why you think the total should be 30 - is it not 60? This one gives an answer of 60: =SUMPRODUCT((A2:A8="C")*(B2:B8="Feb")*(C2:C8+D2:D8 +E2:E8)) Hope this helps. Pete On Feb 21, 11:09 pm, joec wrote: is there a way i can use sumproduct or some other formula to data from the following table for multiple criteria: Associate Month Quest 1 Quest 2 Quest 3 A jan 10 10 10 B jan 9 9 9 B feb 8 8 8 B mar 9 8 8 C jan 7 7 6 C feb 10 10 10 C feb 10 10 10 specifically what i need to do is count the # of times associate C has entries for Feb and return the totl # of all questions and the total of all questions sumed up. The answer for associate C for Feb would be 6 questions for a total of 30. i know this will require 2 different formulas in 2 separate cells, but that is ok. -- joec |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use sumproduct for this?
Compact Formulae
=SUMPRODUCT((A2:A8="C")*(B2:B8="Feb")*(C2:E8<0)) =SUMPRODUCT((A2:A8="C")*(B2:B8="Feb")*(C2:E8)) "Pete_UK" wrote: This will give you the count of questions (6): =SUMPRODUCT((A2:A8="C")*(B2:B8="Feb")*((C2:C8<0)+ (D2:D8<0)+ (E2:E8<0))) Not sure why you think the total should be 30 - is it not 60? This one gives an answer of 60: =SUMPRODUCT((A2:A8="C")*(B2:B8="Feb")*(C2:C8+D2:D8 +E2:E8)) Hope this helps. Pete On Feb 21, 11:09 pm, joec wrote: is there a way i can use sumproduct or some other formula to data from the following table for multiple criteria: Associate Month Quest 1 Quest 2 Quest 3 A jan 10 10 10 B jan 9 9 9 B feb 8 8 8 B mar 9 8 8 C jan 7 7 6 C feb 10 10 10 C feb 10 10 10 specifically what i need to do is count the # of times associate C has entries for Feb and return the totl # of all questions and the total of all questions sumed up. The answer for associate C for Feb would be 6 questions for a total of 30. i know this will require 2 different formulas in 2 separate cells, but that is ok. -- joec |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I use sumproduct for this?
You're welcome - thanks for feeding back.
Pete On Feb 22, 1:26*am, joec wrote: works great thanks!!! -- joec "Pete_UK" wrote: This will give you the count of questions (6): =SUMPRODUCT((A2:A8="C")*(B2:B8="Feb")*((C2:C8<0)+ (D2:D8<0)+ (E2:E8<0))) Not sure why you think the total should be 30 - is it not 60? This one gives an answer of 60: =SUMPRODUCT((A2:A8="C")*(B2:B8="Feb")*(C2:C8+D2:D8 +E2:E8)) Hope this helps. Pete On Feb 21, 11:09 pm, joec wrote: is there a way i can use sumproduct or some other formula to data from the following table for multiple criteria: * * * * Associate * * * Month * * * *Quest 1 * * Quest 2 * *Quest 3 * * * * * * * A * * * * * * * jan * * * * * * 10 * * * * * * 10 * * * * * *10 * * * * * * * B * * * * * * * jan * * * * * * * 9 * * * * * * * 9 * * * * * * * 9 * * * * * * * B * * * * * * * feb * * * * * * * 8 * * * * * * * 8 * * * * * * * 8 * * * * * * * * B * * * * * * * mar * * * * * * 9 * * * * * * * *8 * * * * * * *8 * * * * * * * C * * * * * * * jan * * * * * * * 7 * * * * * * * 7 * * * * * * * 6 * * * * * * * C * * * * * * * feb * * * * * * *10 * * * * * * 10 * * * * * *10 * * * * * * * C * * * * * * * feb * * * * * * *10 * * * * * * 10 * * * * * * 10 specifically what i need to do is *count the # of times associate C has entries for Feb and return the totl # of all questions and the total of all questions sumed up. The answer for associate C for Feb would be 6 questions for a total of 30. i know this will require 2 different formulas in 2 separate cells, but that is ok. -- joec- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
need some help on sumproduct | Excel Discussion (Misc queries) | |||
SUMPRODUCT--what else?? | Excel Discussion (Misc queries) | |||
Sumproduct? | Excel Discussion (Misc queries) | |||
Like Sumproduct, But Different | Excel Worksheet Functions |