Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum based on two conditions
Hello! Please help with data below - sum col B based on top 2 of col C and
group by Col A. T1=70+100=170, T2 = 400+300=700, T3=150+110=260 Thank you so much for helping. Col A Col B Col C T1 100 20% T3 150 15% T2 400 30% T1 400 10% T1 70 50% T2 120 18% T1 150 15% T2 300 30% T3 110 10% |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum based on two conditions
If your data is in A1:C9 and
A13 = T1 A14 = T2 A15 = T3 Enter this in B13 (Confirmed with Control+Shift+Enter after typing it into the formula bar-if you do it right excel will put braces { } around the formula) and copy down. =SUM(($A$1:$A$9=A13)*($C$1:$C$9=LARGE(IF($A$1:$A$ 9=A13,$C$1:$C$9,0),2))*($B$1:$B$9)) "luvgreen" wrote: Hello! Please help with data below - sum col B based on top 2 of col C and group by Col A. T1=70+100=170, T2 = 400+300=700, T3=150+110=260 Thank you so much for helping. Col A Col B Col C T1 100 20% T3 150 15% T2 400 30% T1 400 10% T1 70 50% T2 120 18% T1 150 15% T2 300 30% T3 110 10% |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum based on two conditions
Thank you so very much for your kind help. I appreciate it.
"JMB" wrote: If your data is in A1:C9 and A13 = T1 A14 = T2 A15 = T3 Enter this in B13 (Confirmed with Control+Shift+Enter after typing it into the formula bar-if you do it right excel will put braces { } around the formula) and copy down. =SUM(($A$1:$A$9=A13)*($C$1:$C$9=LARGE(IF($A$1:$A$ 9=A13,$C$1:$C$9,0),2))*($B$1:$B$9)) "luvgreen" wrote: Hello! Please help with data below - sum col B based on top 2 of col C and group by Col A. T1=70+100=170, T2 = 400+300=700, T3=150+110=260 Thank you so much for helping. Col A Col B Col C T1 100 20% T3 150 15% T2 400 30% T1 400 10% T1 70 50% T2 120 18% T1 150 15% T2 300 30% T3 110 10% |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum based on two conditions
Be sure to note Domenic's caveat about more than 2 values being included in
the formula if the percentages are duplicated. It would affect the formula I posted as well. "luvgreen" wrote: Thank you so very much for your kind help. I appreciate it. "JMB" wrote: If your data is in A1:C9 and A13 = T1 A14 = T2 A15 = T3 Enter this in B13 (Confirmed with Control+Shift+Enter after typing it into the formula bar-if you do it right excel will put braces { } around the formula) and copy down. =SUM(($A$1:$A$9=A13)*($C$1:$C$9=LARGE(IF($A$1:$A$ 9=A13,$C$1:$C$9,0),2))*($B$1:$B$9)) "luvgreen" wrote: Hello! Please help with data below - sum col B based on top 2 of col C and group by Col A. T1=70+100=170, T2 = 400+300=700, T3=150+110=260 Thank you so much for helping. Col A Col B Col C T1 100 20% T3 150 15% T2 400 30% T1 400 10% T1 70 50% T2 120 18% T1 150 15% T2 300 30% T3 110 10% |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum based on two conditions
Assuming that A2:C10 contains the data, let E2:E4 contain T1, T2, and
T3, then enter the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER, in F2 and copy down: =SUM(IF(A$2:A$10=E2,IF(ISNUMBER(MATCH(C$2:C$10,LAR GE(IF(A$2:A$10=E2,C$2:C $10),{1,2}),0)),B$2:B$10))) Note that if your data contains the following... T1 100 20% T3 150 15% T2 400 30% T1 400 20% T1 70 20% T2 120 18% T1 150 10% T2 300 30% T3 110 10% ....where the top percentage for T1 is 20%, and there are 3 instances of 20% for T1, the formula will return 570. In article , luvgreen wrote: Hello! Please help with data below - sum col B based on top 2 of col C and group by Col A. T1=70+100=170, T2 = 400+300=700, T3=150+110=260 Thank you so much for helping. Col A Col B Col C T1 100 20% T3 150 15% T2 400 30% T1 400 10% T1 70 50% T2 120 18% T1 150 15% T2 300 30% T3 110 10% |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Averaging Cells Based On Conditions in Neighboring Cells | Excel Discussion (Misc queries) | |||
Total or Count based on multiple conditions | Excel Discussion (Misc queries) | |||
Add cells from a range based on 2 conditions from 2 other ranges | Excel Worksheet Functions | |||
SUM based on multiple conditions - SORRY, URGENT!!! | Excel Worksheet Functions | |||
SUMIF based on 2 conditions | Excel Worksheet Functions |