excelexcel-formula

Excel: Sum duplicates values only once and only on visible cells

Given the following FILTERED table :

TYPE NUM
A 3
A 3
A 3
B 5
B 5

I need to find the sum of column NUM without including the duplicate values. I currently have the following solution based on another post:

=SUMPRODUCT(B2:B6/COUNTIFS(A2:A6,A2:A6))

In this case, the result is 8, which is correct when no filters have been applied.

Now if I filter the column to show only A in TYPE, it will still show me 8. The issue I am having is I need the sum to be adjusted based on what is visible. After applying the filter to show only A, I should be getting 3 and if I filter to show B only I should get 5. I've tried AGGREGATE and SUBTOTAL and haven't gotten anywhere.

Is there a way to adjust the above formula to VISIBLE cells only?

Thanks,

Solution

  • So it's a rather complicated formula involving both SUMPRODUCT(), SUBTOTAL() and OFFSET(). More information here

    To implement this to your situation you would get:

    =SUMPRODUCT((B2:B6/COUNTIFS(A2:A6,A2:A6))*(SUBTOTAL(3,OFFSET(B2,ROW(B2:B6)-MIN(ROW(B2:B6)),0))))

    Another option would be (in your case if values for each criteria is the same)

    =SUMPRODUCT((A2:A6<>A3:A7)*(B2:B6)*(SUBTOTAL(3,OFFSET(B2,ROW(B2:B6)-MIN(ROW(B2:B6)),0))))

    enter image description here

    In my example I'll filter out both A and B

    enter image description here

    A formula to be used on dynamic ranges would look like:

    =SUMPRODUCT((B2:INDEX(B:B,COUNTA(B:B))/COUNTIFS(A2:INDEX(A:A,COUNTA(A:A)),A2:INDEX(A:A,COUNTA(A:A))))*(SUBTOTAL(3,OFFSET(B2,ROW(B2:INDEX(B:B,COUNTA(B:B)))-MIN(ROW(B2:INDEX(B:B,COUNTA(B:B)))),0))))