Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maximum value as well as Sum
Hi,
I have the following data in a list: Col A Col B Col C Col D B 9 1 Red C 12 3 Green A 13 1 Red B 14 2 Violet C 15 4 Yellow B 16 2 Yellow A 18 2 Green B 19 2 Green C 21 5 Red I want to retain only those rows whe 1. For each unique value in Column A, the value in Column B is maximum and Column C in that row has the sum of col c for that unique value. The output from the above list should be: Col A Col B Col C Col D A 18 3 Green B 19 11 Green C 21 8 Red Thanks in advance for all the help. Regards, Raj |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maximum value as well as Sum
1. Select Col A
Choose Data|Filter|Advanced Filter Choose Copy to H1 Unique Records only You will get the unique values from Col A into Col H in I2 enter =MAX(IF($A$2:$A$10=A2,$B$2:$B$10,0)) and press CTRL-SHIFT-ENTER to get the MAX value for the value in H2 and copy down In J2 enter =SUMPRODUCT(--($A$2:$A$10=H2),$C$2:$C$10) and copy down to get the Sum for the unique value in H In K2 enter =INDIRECT("D"&MATCH(I2,(IF($A$2:$A$10=H2,$B$2:$B$1 0,"")),0)+1) btw I got the following; ColA Max Sum Color B 19 7 Green C 21 12 Red A 18 3 Green "Raj" wrote: Hi, I have the following data in a list: Col A Col B Col C Col D B 9 1 Red C 12 3 Green A 13 1 Red B 14 2 Violet C 15 4 Yellow B 16 2 Yellow A 18 2 Green B 19 2 Green C 21 5 Red I want to retain only those rows whe 1. For each unique value in Column A, the value in Column B is maximum and Column C in that row has the sum of col c for that unique value. The output from the above list should be: Col A Col B Col C Col D A 18 3 Green B 19 11 Green C 21 8 Red Thanks in advance for all the help. Regards, Raj |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I set a maximum value (e.g. cap at 200) | Excel Worksheet Functions | |||
Maximum | Excel Discussion (Misc queries) | |||
Return Maximum from Column directly above Maximum in Row | Charts and Charting in Excel | |||
maximum | Excel Worksheet Functions | |||
Specify Maximum Value | Excel Discussion (Misc queries) |