SUMIFS Unique Values

[ramibassad]

Hello...Can someone help me get the desired result in the table below? I want Excel to only sum unique billed amount values based on a claim number. A simple SUMIF formula would return $9,500 but if we exclude the duplicate dollar amounts, the desired value of $4,750 is returned. Anyway to do this?

Claim #	Date of Service	Billed Amount
862735614	10/5/2021	1000
862735614	10/5/2021	1500
862735614	10/5/2021	2000
862735614	10/5/2021	250
862735614	10/5/2021	1000
862735614	10/5/2021	1500
862735614	10/5/2021	2000
862735614	10/5/2021	250
535246347	10/9/2019	1000
Input Claim #	862735614
Desired Result	$ 4,750.00

 

[Eric W]

Try:

Book1 (version 1).xlsb
	A	B	C
1	Claim #	Date of Service	Billed Amount
2	862735614	10/5/2021	1000
3	862735614	10/5/2021	1500
4	862735614	10/5/2021	2000
5	862735614	10/5/2021	250
6	862735614	10/5/2021	1000
7	862735614	10/5/2021	1500
8	862735614	10/5/2021	2000
9	862735614	10/5/2021	250
10	535246347	10/9/2019	1000
11
12	Input Claim #	862735614
13	Desired Result	$4,750.00
Sheet4

Cell Formulas
Range	Formula
B13	B13	=SUMPRODUCT(C2:C10,--(A2:A10=B12),--IFERROR(MATCH(B12&"|"&C2:C10,A2:A10&"|"&C2:C10,0)=ROW(C2:C10)-ROW(C2)+1,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Joe4]

Just something to think about...

Given that it appears that you can have multiple lines for the same Claim # and Date (not sure if these are different line items for a particular invoice or what), are you saying that it is not possible to have two distinct line items that just happened to be the same dollar amount (and hence, should really be counted separately, even though they may be the same amount)?

Don't know if that is possible, as we really do not know the background on this.
But just something to think about, in case those three fields are really not enough to determine what is a duplicate and what is not.

 

[lrobbo314]

Another way that seems to work.

Book1 (version 2).xlsb
	A	B	C
1	Claim #	Date of Service	Billed Amount
2	862735614	10/5/2021	1000
3	862735614	10/5/2021	1500
4	862735614	10/5/2021	2000
5	862735614	10/5/2021	250
6	862735614	10/5/2021	1000
7	862735614	10/5/2021	1500
8	862735614	10/5/2021	2000
9	862735614	10/5/2021	250
10	535246347	10/9/2019	1000
11
12	Input Claim #	862735614
13	Desired Result	4750
Sheet6

Cell Formulas
Range	Formula
B13	B13	=SUMPRODUCT((A2:A11=B12)*IF(FREQUENCY(C2:C11,C2:C11)>0,1,0)*C2:C11)