CODEWITHSUNDEEP
excelexcel-formula
Michi
Michi

Reputation: 5481

Sum up unique values in column per criteria in other column (if columns are filtered)

enter image description here

With reference to this quesion in Cell B1 I use the formula =SUM(INDEX(UNIQUE(A2:B9),,2)) in order to only sumup the unique values per product.

All this works perfectly.

Now, I set a filter in Column A and exclude for example Product_A and Product_C from the list:

enter image description here

As you can see the 1.900 in Cell B1 remains. However, I only want to apply the formula in Cell B1 to the filtered data which means that - in this case - the value in Cell B1 should be 1.000.

Normally, I could achieve this with a SUBTOTAL formula but I do not have any clue how I can combine the UNIQUE and INDEX function with the SUBTOTAL.

Upvotes: 0

Views: 715

Answers (1)

JvdV
JvdV

Reputation: 75990

Bit of a stretch to be honest:

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

You could always just avoid the volatile function and use:

=SUM(INDEX(UNIQUE(FILTER(A2:B9,(A2:A9<>"Product_A")*(A2:A9<>"Product_C"))),0,2))

Upvotes: 2

Related Questions