Sumproduct/Sumifs????? unique

[TNRex24]

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......

Thanks in advance

 

[Marcelo Branco]

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.

 

[Aladin Akyurek]

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......

Thanks in advance

Control+shift+enter, not just enter:

=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))

 

[TNRex24]

It worked Aladin! Thank you.

 

[Aladin Akyurek]

It worked Aladin! Thank you.

You are welcome.