Sum up unique values in column per criteria in other column (if values are on the left side of the criteria column)

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

With reference to the answer from this question I wanted to sum up the unique values per product in Cell A1.
Therefore, I tried to go with this formula:

A1 =SUM(INDEX(UNIQUE(IF(SUBTOTAL(2;OFFSET(B2:B9;ROW(B2:B9)-ROW(B2);1;1));A2:B9));;1))

However, as a result now I get 0 instead of 1.900.
I assume the issue results because I have the values on the left side of the criteria column.
Do you have any idea how I need to modify the formula to also make it work in the displayed column order?

1
excelexcel-formula
It looks as though the original question was about unique sum with filtering and that's where the subtotal came from - are you still filtering in this question? – Tom Sharpe
Yeah, the filter option should still be possible. – Michi
Why not sumifs()? – Solar Mike
Of course 3rd time lucky... ie post again :) – Solar Mike

1 Answers

1
votes

All you needed to do was to change the 1 into -1 on the OFFSET():

=SUM(INDEX(UNIQUE(IF(SUBTOTAL(2,OFFSET(B2:B9,ROW(B2:B9)-ROW(B2),-1,1)),A2:B9)),,1))