Sum values(column C) based on unique value(column A) after filtering(column B)

Question

I need some help creating a formula. I have an excel file with 3 columns and I need to sum values of column C, based on the unique values of column A even if I filter something in column B.

Col A	Col B	Col C
AAA	A	10
AAA	A	10
BBB	B	20
BBB	B	20
CCC	C	10
DDD	D	10
DDD	D	10
EEE	A	20

I achieved that using the formula:

=SUMPRODUCT((C8:C8/COUNTIF(A8:A8;A8:A8)))

Which gives me the result "70" but when I use filter in column B, the result is not updated. E.G. if I filter column B and choose to bring me only the "A" the result should be "30", but it remains "70".

Could you please help me?

Thanks in advance, Elias

Answer 1

If ms365 is applicable, try to utilize SUBTOTAL() nested within MAP():

Formula in E1:

=SUM(UNIQUE(FILTER(A2:C9,MAP(A2:A9,LAMBDA(x,SUBTOTAL(103,x))))))

---

Option 1 for Excel 2016: SUBTOTAL() in an helper column:

OP's using ms Excel 2016, therefor he could also just apply the SUBTOTAL(103,<ref>) to an helper column:

Formula in D2 and dragged down:

=SUBTOTAL(103,B2)

Formula in F1:

=SUMPRODUCT((C2:C9/COUNTIFS(A2:A9,A2:A9))*D2:D9)

---

Option 2 for Excel 2016: Measure inside Power Pivot Table:

You could also apply a Power Pivot Table.

• Select a cell from given table and on the 'Power Pivot' tab click to 'Add to Data Model';

• If any pop-up opens, you can close this for now;

• On the 'Power Pivot' tab now click 'Measures'>'New Measure';

• On the window that opens, select the previously added table at the top, find a suitable name for your measure and use the following formula:

  =SUMX(DISTINCT(Table1[Col A]),FIRSTNONBLANK(Table1[Col C],0))

• Click 'OK', then go back to the 'Power Pivot' tab to click 'Manage';

• On the 'Home' tab now click to insert a PivotTable, choose a suitable place for the output;

• Set ColA to rows, ColB to Filter and your measure to Values;

• The output should look something like this:

Now instead of filtering your original data, filter cell F1. You still have the option to select multiple options there.

A more convoluted option perhaps.

Answer 2

Here is one way without using any LAMBDA() helper function(Applicable to MS365 exclusively):

Before:

---

After:

---

=LET(α, A2:C9, SUM(TAKE(UNIQUE(FILTER(α,SUBTOTAL(3,OFFSET(α,SEQUENCE(ROWS(α))-1,0,1)))),,-1)))

---

For Excel 2016 one can use the following formula:

---

• Formula used in cell E1

=SUMPRODUCT(
  IF(SUBTOTAL(3,OFFSET($A$2:$C$9,ROW($A$2:$C$9)-MIN(ROW($A$2:$C$9)),,1)),
  C2:C9/COUNTIFS(A2:A9,A2:A9,B2:B9,B2:B9)))

---

Answer 3

Using this as a base: CountIf With Filtered Data.

Try:

=SUMPRODUCT(($A$2:$A$9=$A12)*(SUBTOTAL(3,OFFSET($C$2:$C$9,ROW($C$2:$C$9)-MIN(ROW($C$2:$C$9)),,1)))*$C$2:$C$9)  

Answer 4

My joy was too hasty since SEQUENCE is also W365..
While I was trying to take Mayukh's formula apart bit by bit and getting stumped as to why =SUBTOTAL(3;OFFSET(A2:C9;SEQUENCE(ROWS(A2:C9))-1;0;1)) gave me a visible understanding but OFFSET(A2:C9;SEQUENCE(ROWS(A2:C9))-1;0;1) just gave me a series of #VALUE! errors, I did realize I could maybe use the SUBTOTAL part and shove it into OP's formula.

It went rather well, formula in G1:

=SUMPRODUCT(C2:C9*(SUBTOTAL(3;OFFSET(A2:C9;SEQUENCE(ROWS(A2:C9))-1;0;1))>0)/COUNTIF(A2:A9;A2:A9))

Formula in F1 is Mayukh's formula and the rest below were some testing I previously mentioned.