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

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

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

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

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