| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum unique values based on criteria
Hi all
The following is my data Name Code amount John Red 3,700 Smith Red 3,700 Apple Brown 2,100 Orange Brown 2,100 James Green 37,500 Hick Green 37,500 Asish Yellow 2,100 David Yellow 2,100 Gomes Yellow 2,100 Lorry 3,700 Jain 3,700 I want to sum all the amounts for unique code values. e.g. red+brown+green+yellow = 3700 +2100 +37500 + 2100 = 45400. My data is not sorted on code. For code = "", i can get the sum using sumproduct. Any suggestions Regards Sreedhar |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum unique values based on criteria
One way:
=SUMIF(B2:B12,"<",C2:C12) Hope this helps. Pete On Apr 28, 11:32*am, yshridhar wrote: Hi all The following is my data Name * *Code * *amount John * *Red * * *3,700 Smith * Red * * *3,700 Apple * Brown * *2,100 Orange *Brown * *2,100 James * Green * *37,500 Hick * *Green * *37,500 Asish * Yellow * 2,100 David * Yellow * 2,100 Gomes * Yellow * 2,100 Lorry * * * * * *3,700 Jain * * * * * * 3,700 I want to sum all the amounts for unique code values. *e.g. red+brown+green+yellow = 3700 +2100 +37500 + 2100 = 45400. My data is not sorted on code. For code = "", i can get the sum using sumproduct. Any suggestions Regards Sreedhar |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum unique values based on criteria
Assuming that A2:C12 contains the data, try the following formula that
needs to be confirmed with CONTROL+SHIFT+ENTER... =SUM(IF(FREQUENCY(IF(B2:B12<"",MATCH("~"&B2:B12,B 2:B12&"",0)),ROW(B2:B12 )-ROW(B2)+1),C2:C12)) Hope this helps! In article , yshridhar wrote: Hi all The following is my data Name Code amount John Red 3,700 Smith Red 3,700 Apple Brown 2,100 Orange Brown 2,100 James Green 37,500 Hick Green 37,500 Asish Yellow 2,100 David Yellow 2,100 Gomes Yellow 2,100 Lorry 3,700 Jain 3,700 I want to sum all the amounts for unique code values. e.g. red+brown+green+yellow = 3700 +2100 +37500 + 2100 = 45400. My data is not sorted on code. For code = "", i can get the sum using sumproduct. Any suggestions Regards Sreedhar |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum unique values based on criteria
Try this:
=SUMPRODUCT((Code<"")/COUNTIF(Code,Code&"")*amount) "yshridhar" wrote: Hi all The following is my data Name Code amount John Red 3,700 Smith Red 3,700 Apple Brown 2,100 Orange Brown 2,100 James Green 37,500 Hick Green 37,500 Asish Yellow 2,100 David Yellow 2,100 Gomes Yellow 2,100 Lorry 3,700 Jain 3,700 I want to sum all the amounts for unique code values. e.g. red+brown+green+yellow = 3700 +2100 +37500 + 2100 = 45400. My data is not sorted on code. For code = "", i can get the sum using sumproduct. Any suggestions Regards Sreedhar |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum unique values based on criteria
Thanks Mama. It works like a charm.
Regards Sreedhar "Teethless mama" wrote: Try this: =SUMPRODUCT((Code<"")/COUNTIF(Code,Code&"")*amount) "yshridhar" wrote: Hi all The following is my data Name Code amount John Red 3,700 Smith Red 3,700 Apple Brown 2,100 Orange Brown 2,100 James Green 37,500 Hick Green 37,500 Asish Yellow 2,100 David Yellow 2,100 Gomes Yellow 2,100 Lorry 3,700 Jain 3,700 I want to sum all the amounts for unique code values. e.g. red+brown+green+yellow = 3700 +2100 +37500 + 2100 = 45400. My data is not sorted on code. For code = "", i can get the sum using sumproduct. Any suggestions Regards Sreedhar |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count Unique records based on the Criteria in another colum | Excel Worksheet Functions | |||
| Count unique values based on multiple criteria | Excel Discussion (Misc queries) | |||
| how to count unique values in excel based on multiple criteria | Excel Worksheet Functions | |||
| Count unique values and create list based on these values | Excel Worksheet Functions | |||
| how to count unique values in excel based on criteria | Excel Worksheet Functions | |||
Linear Mode
