CODEWITHSUNDEEP
excelexcel-formula
Michi
Michi

Reputation: 5471

Sum up unique values in column per criteria in other column

   |       A                 B           
---|----------------------------------------- 
1  |    Total              1.900
---|-----------------------------------------
2  |    Product_A           700        
3  |    Product_A           700  
---|-----------------------------------------      
4  |    Product_B           300        
---|-----------------------------------------
5  |    Product_C           200      
6  |    Product_C           200      
---|------------------------------------------
7  |    Product_D           700         
8  |    Product_D           700      
9  |    Prodcut_D           700  
10 |

In Cell B1 I want to sum the unique values in Column B per product in Column A.

B1 = 700 + 300 + 200 + 700

Currently, in Cell B1 I use the formular from this question which is =SUM(UNIQUE(B2:B9)).
However, with this formula I get 1.200 as result because it does not include the 700 from Product_D.

How do I have to modify the formula so it sums up the values unique per product?

Upvotes: 2

Views: 1499

Answers (2)

Harun24hr
Harun24hr

Reputation: 36770

As you have UNIQUE() function then you can use it with index and sum like-

=SUM(INDEX(UNIQUE(A2:B9),,2))

enter image description here

Upvotes: 0

LeelaPrasad
LeelaPrasad

Reputation: 466

Try SUMPRODUCT instead.

=SUMPRODUCT(B2:B9/COUNTIF(A2:A9,A2:A9))

It will get you the sum of values in B for unique values in A.

Note: this is assuming,the values in B2:B9 are all numeric. If not you will get a #VALUE error

Upvotes: 3

Related Questions