Excel how to add values in Column C if combination of values in column A and C is unique-CodePudding

I have an Excel table listing rooms, type of windows and how much windows are in that room: Example:

        COL-A| COL-B  | Col-C
Row1 |  Room | Window | Qty
Row2 |  A    | W1     | 1
Row3 |  A    | W2     | 1
Row4 |  A    | W1     | 1
Row5 |  B    | W1     | 1
Row6 |  B    | W1     | 1
Row7 |  B    | W1     | 1
Row8 |  B    | W1     | 1
...

I need to get a list telling how many Windows of each typ there is in each room:

       COL-A | COL-B  | Col-C
Row1 |  Room | Window | Qty
Row1 |  A    | W1     | 2
Row1 |  A    | W2     | 1
Row1 |  B    | W1     | 4
...

It means I have to add values in Column C (QTY) if the combination of values in Column A and B are the same.

I have tryed all sort of cformula combinations like =SUMIFS(UNIQUE(A2:A100);AND;UNIQUE(B:100)) However without succes.

Any help would be appreciated

CodePudding user response：

I just inserted a Pivot Table, and clicked on all fields to add to report, this is the screenshot of the result:

(The sigma values "Sum of Quantity is generated automatically, that's how basic this is)

CodePudding user response：

Actually you need pivot table. You can also achieve it by formula. Try the following formula.

=LET(x,UNIQUE(A2:B8),y,SUMIFS(C2:C8,A2:A8,INDEX(x,,1),B2:B8,INDEX(x,,2)),HSTACK(x,y))