# Sumproduct/Sumifs????? unique

#### TNRex24

##### New Member
I have 3 columns needing the sum from column C without duplicates if column B ='s IL but exclude if columnA is duplicated.
ColumnA(ID_Num) ColumnB(State) ColumnC(Spend)
1 IL 50
2 CT 30
3 IL 25
4 MI 80
1 IL 50

should equal 75
I am using =sumifs(C2:C5,B2:B5,"IL") and I get the total but includes the duplicate. I'm sure I'm suppose to use a /countif or sumproducts but I have been unsuccessful and I'm waving the white flag. - To new at this......

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Hi and welcome to Mr Excel Forum

Maybe this

=SUMPRODUCT(--(MATCH(A2:A6,A2:A6,0)=ROW(A2:A6)-ROW(A2)+1),--(B2:B6="IL"),C2:C6)

M.

I have 3 columns needing the sum from column C without duplicates if column B ='s IL but exclude if columnA is duplicated.
ColumnA(ID_Num) ColumnB(State) ColumnC(Spend)
1 IL 50
2 CT 30
3 IL 25
4 MI 80
1 IL 50

should equal 75
I am using =sumifs(C2:C5,B2:B5,"IL") and I get the total but includes the duplicate. I'm sure I'm suppose to use a /countif or sumproducts but I have been unsuccessful and I'm waving the white flag. - To new at this......

Control+shift+enter, not just enter:
Rich (BB code):
``````=SUM(IF(FREQUENCY(IF(B2:B6="IL",
MATCH(B2:B6&"|"&C2:C6,B2:B6&"|"&C2:C6,0)),ROW(B2:B6)-ROW(B2)+1),
C2:C6))
``````

Replies
5
Views
598
Replies
26
Views
2K
Replies
0
Views
272
Replies
13
Views
380
Replies
1
Views
230

1,203,675
Messages
6,056,683
Members
444,883
Latest member
garyarubin

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back