Sum if, I think

[Sammi8796]

I have a column (B) that lists material numbers. In column B it may or may not repeat the material number. I have the following formula set up so it will count how many different material numbers are listed and so it won't count the same number twice.

=SUMPRODUCT((B2:B103<>"")/COUNTIF(B2:B103,B2:B103&""))

I need a variation of this formula for another cell. I still want to sum up the material numbers in column B but I need it to depend on whats in column C. In column C it will either say "A" or "B" next to each material number. I want it to look at column C and count the material numbers only once in column B. So I want to sum up the "B" material numbers and the "A" material numbers. Remember that the material numbers may appear more than once. Hope that makes sense. Any help would be appreciated. Thanks.

 

[AlphaFrog]

Excel Workbook
Cell	Formula
	=SUM(IF(FREQUENCY(IF(((LEN(B2:B103)>0)*(C2:C103="A")),MATCH(B2:B103,B2:B103,0),""), IF(LEN(B2:B103)>0,MATCH(B2:B103,B2:B103,0),""))>0,1))
Array Formula

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[Sammi8796]

Thank you so much! Works perfectly!

 

[Aladin Akyurek]

I have a column (B) that lists material numbers. In column B it may or may not repeat the material number. I have the following formula set up so it will count how many different material numbers are listed and so it won't count the same number twice.

=SUMPRODUCT((B2:B103<>"")/COUNTIF(B2:B103,B2:B103&""))

I need a variation of this formula for another cell. I still want to sum up the material numbers in column B but I need it to depend on whats in column C. In column C it will either say "A" or "B" next to each material number. I want it to look at column C and count the material numbers only once in column B. So I want to sum up the "B" material numbers and the "A" material numbers. Remember that the material numbers may appear more than once. Hope that makes sense. Any help would be appreciated. Thanks.

Less expensive...

Control+shift+enter, not just enter...

=SUM(IF(FREQUENCY(IF(B2:B103<>"",IF(C2:C103="A",
   MATCH("~"&B2:B103,B2:B103&"",0))),ROW(B2:B103)-ROW(B2)+1),1))