Sumif based on multiple conditions in excel
| Material | Region | Sales |
|---|---|---|
| M1 | Region1 | 100 |
| M1 | Region2 | 200 |
| M1 | Region4 | 200 |
| M2 | Region3 | 150 |
| M3 | Region3 | 75 |
| M4 | Region6 | 200 |
| M5 | Region3 | 100 |
From the table above i have to calculate the sum of sales from different groups. First condition of a group is that it can have same Material value but different Region values, so M1-Region1, M1-Region2,M1-Region4 , sum of these combinations would be 500, all of these combinations should have 500 on the sum column. Second condition for a group is that it can have different Material values but same Region values, so M2-Region3, M3-Region3,M5-Region3- sum of these combinations from sales column would be 325, all of these combinations should show 325 on the sum column, M4-Region 6 doesnt have any other combinations so it remains a stand alone combination, sum column value should be same as Sales column value which is 200. So the output would like a below table. Could you please help to have a sumif formula that address these conditions
Output table should be like this
| Material | Region | Sales | Sum |
|---|---|---|---|
| M1 | Region1 | 100 | 500 |
| M1 | Region2 | 200 | 500 |
| M1 | Region4 | 200 | 500 |
| M2 | Region3 | 150 | 325 |
| M3 | Region3 | 75 | 325 |
| M4 | Region6 | 200 | 200 |
| M5 | Region3 | 100 | 325 |
Please advise on this problem. Thank you
| Material | Region | Sales | Formula |
|---|---|---|---|
| M1 | Region1 | 100 | 500 |
| M1 | Region2 | 200 | 500 |
| M1 | Region4 | 200 | 500 |
| M2 | Region3 | 150 | 325 |
| M3 | Region3 | 75 | 325 |
| M4 | Region6 | 200 | 200 |
| M5 | Region3 | 100 | 325 |
| M6 | Region5 | 100 | 600 |
| M7 | Region7 | 200 | 200 |
| M8 | Region5 | 200 | 500 |
| M8 | Region5 | 300 | 500 |
[![enter image description here][3]][3]
EDIT: This will get the results you need in older excel:
=SUM(INDEX(C:C,AGGREGATE(15,6,ROW($C$2:$C$9)/(ISNUMBER(MATCH($A$2:$A$9,INDEX(A:A,AGGREGATE(15,6,ROW($C$2:$C$9)/ISNUMBER(MATCH($B$2:$B$9,INDEX(B:$B,AGGREGATE(15,6,ROW($C$2:$C$9)/($A$2:$A$9=A2),ROW($A$1:INDEX($A:$A,SUMPRODUCT(N($A$2:$A$9=A2)))))),0)),ROW($A$1:INDEX($A:$A,SUMPRODUCT(--ISNUMBER(MATCH($B$2:$B$9,INDEX(B:$B,AGGREGATE(15,6,ROW($C$2:$C$9)/($A$2:$A$9=A2),ROW($A$1:INDEX($A:$A,SUMPRODUCT(N($A$2:$A$9=A2)))))),0))))))),0))),ROW($A$1:INDEX($A:$A,SUMPRODUCT(--ISNUMBER(MATCH($A$2:$A$9,INDEX(A:A,AGGREGATE(15,6,ROW($C$2:$C$9)/ISNUMBER(MATCH($B$2:$B$9,INDEX(B:$B,AGGREGATE(15,6,ROW($C$2:$C$9)/($A$2:$A$9=A2),ROW($A$1:INDEX($A:$A,SUMPRODUCT(N($A$2:$A$9=A2)))))),0)),ROW($A$1:INDEX($A:$A,SUMPRODUCT(--ISNUMBER(MATCH($B$2:$B$9,INDEX(B:$B,AGGREGATE(15,6,ROW($C$2:$C$9)/($A$2:$A$9=A2),ROW($A$1:INDEX($A:$A,SUMPRODUCT(N($A$2:$A$9=A2)))))),0))))))),0))))))))
I know, It's a wall of formula.
Basically it filters out all Regions for current row's Machine. Then it filters out all Machines associated with the filtered Regions. Finally it checks which rows belong to these filtered Regions and sum the rows of the sales column for these columns.
Office 365 would be like this:
=MAP(A2:A9,LAMBDA(a,LET(r,FILTER(B2:B9,A2:A9=a),LET(m,UNIQUE(FILTER(A2:A9,ISNUMBER(XMATCH(B2:B9,r)))),SUM(FILTER(C2:C9,ISNUMBER(XMATCH(A2:A9,m))))))))
or for the new added table:
=SUM(INDEX(C:C,AGGREGATE(15,6,ROW($C$2:$C$12)/(ISNUMBER(MATCH($A$2:$A$12,INDEX(A:A,AGGREGATE(15,6,ROW($C$2:$C$12)/ISNUMBER(MATCH($B$2:$B$12,INDEX(B:$B,AGGREGATE(15,6,ROW($C$2:$C$12)/($A$2:$A$12=A2),ROW($A$1:INDEX($A:$A,SUMPRODUCT(N($A$2:$A$12=A2)))))),0)),ROW($A$1:INDEX($A:$A,SUMPRODUCT(--ISNUMBER(MATCH($B$2:$B$12,INDEX(B:$B,AGGREGATE(15,6,ROW($C$2:$C$12)/($A$2:$A$12=A2),ROW($A$1:INDEX($A:$A,SUMPRODUCT(N($A$2:$A$12=A2)))))),0))))))),0))),ROW($A$1:INDEX($A:$A,SUMPRODUCT(--ISNUMBER(MATCH($A$2:$A$12,INDEX(A:A,AGGREGATE(15,6,ROW($C$2:$C$12)/ISNUMBER(MATCH($B$2:$B$12,INDEX(B:$B,AGGREGATE(15,6,ROW($C$2:$C$12)/($A$2:$A$12=A2),ROW($A$1:INDEX($A:$A,SUMPRODUCT(N($A$2:$A$12=A2)))))),0)),ROW($A$1:INDEX($A:$A,SUMPRODUCT(--ISNUMBER(MATCH($B$2:$B$12,INDEX(B:$B,AGGREGATE(15,6,ROW($C$2:$C$12)/($A$2:$A$12=A2),ROW($A$1:INDEX($A:$A,SUMPRODUCT(N($A$2:$A$12=A2)))))),0))))))),0))))))))