Home |
Search |
Today's Posts |
#1
|
|||
|
|||
SUMIF with 2 conditions
Hi I need to do a function, I have the following columns and some sample data
Name Week Number. Estimated Hours Yvonne 4 2 Yvonne 4 3 Simon 4 5 Yvonne 5 1 What i want to do is, for each person i want to some their total estimated hours for week 4. So in pseudocode im trying to do this: SUM the estimated hours where the name = Yvonne and the week number = 4. The answer should be 5 for this example Can someone please help me to do this, im struggling. Thanks. Simon |
#2
|
|||
|
|||
=SUMPRODUCT(--(A2:A10="Yvonne"),--(B2:B10=4),C2:C10)
or =SUMPRODUCT(--(A2:A10=D2),--(B2:B10=E2),C2:C10) where D2 would hold the names and E2 the week numbers -- Regards, Peo Sjoblom (No private emails please) "Simon" wrote in message ... Hi I need to do a function, I have the following columns and some sample data Name Week Number. Estimated Hours Yvonne 4 2 Yvonne 4 3 Simon 4 5 Yvonne 5 1 What i want to do is, for each person i want to some their total estimated hours for week 4. So in pseudocode im trying to do this: SUM the estimated hours where the name = Yvonne and the week number = 4. The answer should be 5 for this example Can someone please help me to do this, im struggling. Thanks. Simon |
#3
|
|||
|
|||
Try...
=SUMPRODUCT(--($A$2:$A$5="Yvonne"),--($B$2:$B$5=4),$C$2:$C$5) or =SUMPRODUCT(--($A$2:$A$5=E2),--($B$2:$B$5=F2),$C$2:$C$5) ....where E2 contains the name of interest, and F2 contains the week number of interest. Hope this helps! In article , "Simon" wrote: Hi I need to do a function, I have the following columns and some sample data Name Week Number. Estimated Hours Yvonne 4 2 Yvonne 4 3 Simon 4 5 Yvonne 5 1 What i want to do is, for each person i want to some their total estimated hours for week 4. So in pseudocode im trying to do this: SUM the estimated hours where the name = Yvonne and the week number = 4. The answer should be 5 for this example Can someone please help me to do this, im struggling. Thanks. Simon |
#4
|
|||
|
|||
Hi!
Try this: =SUMPRODUCT(--(A1:A10="persons_name"),--(B1:B10=4),C1:C10) Biff "Simon" wrote in message ... Hi I need to do a function, I have the following columns and some sample data Name Week Number. Estimated Hours Yvonne 4 2 Yvonne 4 3 Simon 4 5 Yvonne 5 1 What i want to do is, for each person i want to some their total estimated hours for week 4. So in pseudocode im trying to do this: SUM the estimated hours where the name = Yvonne and the week number = 4. The answer should be 5 for this example Can someone please help me to do this, im struggling. Thanks. Simon |
#5
|
|||
|
|||
Alternately you could use
=SUMPRODUCT((A2:A10="Yvonne")*(B2:B10=4),C2:C10) Joining conditions with "*" does the same thing as prefixing each condition with "--"; it converts the boolean condition array to 0's and 1's. Which form is preferable is largely a matter of taste. I prefer this form because - it reduces the formula length by 2 characters per condition - IMHO it is easier for a new user to understand (some will disagree) - It generalizes to more complicated combinations of conditions. Use "+" instead of "*" to join the conditions with OR instead of AND. If you combine more than two conditions, some with "*" and some with "+", you can use parentheses to control the order of evaluation. Jerry Simon wrote: Hi I need to do a function, I have the following columns and some sample data Name Week Number. Estimated Hours Yvonne 4 2 Yvonne 4 3 Simon 4 5 Yvonne 5 1 What i want to do is, for each person i want to some their total estimated hours for week 4. So in pseudocode im trying to do this: SUM the estimated hours where the name = Yvonne and the week number = 4. The answer should be 5 for this example Can someone please help me to do this, im struggling. Thanks. Simon |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF based on two conditions | Excel Worksheet Functions | |||
sumif with 2 conditions ?? can this be done?? | Excel Worksheet Functions | |||
SUMIF with two conditions | Excel Discussion (Misc queries) | |||
sumif with two conditions | Excel Worksheet Functions | |||
SUMIF based on 2 conditions | Excel Worksheet Functions |