SUMIF for Unique Values

[Dmcnich15]

Hi Guys,

Ive tried playing around and I cant get it to work correctly. I have the original formula below but now I need it to sum if column A is unique. I tried with frequency but i cant seem to get it to work.

=SUMIFS('Nego Stats'!$AM:$AM,'Nego Stats'!$F:$F,"New SA",'Nego Stats'!$Q:$Q,"CM")

I want it to be the below
If column F = New SA
If Column Q = CM
if Column A is unique
then add cell in column AM to sum

Let me know if you have any questions and thank you!

 

[63falcondude]

Is a helper column acceptable? If so, try

Excel 2010
	A	B	C	D	E	F	G
1	Column A	Column B (F)	Column C (Q)	Sum column (AM)	Helper column		Sum with criteria
2	abc	Old SA	AB	123			556
3	swe	New SA	CM	456	Unique
4	abc	New SA	AB	789
5	def	New SA	CM	100	Unique
6	dre	Old SA	CM	200	Unique
7	ghi	Old SA	CM	300	Unique
Sheet2

Cell Formulas
Range	Formula
G2	=SUMIFS(D:D,B:B,"New SA",C:C,"CM",E:E,"Unique")
E2	=IF(COUNTIF(A:A,A2)=1,"Unique","")

Obviously, you will have to change the ranges to fit your data.

 

[63falcondude]

If a helper column is not acceptable, try

=SUMPRODUCT((COUNTIF(A2:A7,A2:A7)=1)*(B2:B7="New SA")*(C2:C7="CM")*(D2:D7))

Note that this formula refers to the table in post #2.