excel - Sum up values per row based on a flexible array in other column - Stack Overflow

admin管理员组

文章数量:1125091

0	A	B	C	D	E	F	G	H	I
1	Employee	Team	Revenue_A	Revenue_B		Filter	T_01
2	E_01	T_01	500	20
3	E_01	T_01	600	30		Employee	Total	Revenue_A	Revenue_B
4	E_01	T_01	100	70		E_01	1320	1200	120
5	E_02	T_01	800	10		E_02	1090	1000	90
6	E_02	T_01	200	80		E_04	1135	1100	35
7	E_03	T_02	400	45
8	E_03	T_02	300	30
9	E_03	T_02	500	25
10	E_03	T_02	100	50
11	E_04	T_01	300	20
12	E_04	T_01	800	15

0	A	B	C	D	E	F	G	H	I
1	Employee	Team	Revenue_A	Revenue_B		Filter	T_01
2	E_01	T_01	500	20
3	E_01	T_01	600	30		Employee	Total	Revenue_A	Revenue_B
4	E_01	T_01	100	70		E_01	1320	1200	120
5	E_02	T_01	800	10		E_02	1090	1000	90
6	E_02	T_01	200	80		E_04	1135	1100	35
7	E_03	T_02	400	45
8	E_03	T_02	300	30
9	E_03	T_02	500	25
10	E_03	T_02	100	50
11	E_04	T_01	300	20
12	E_04	T_01	800	15

In Range F4:F6 I filter the Columns A:B based on the criteria entered in Cell G1 using this formula:

F4 = LET(
a,FILTER($A:$A,$B:$B=$G$1),
UNIQUE(CHOOSECOLS(a,1)))

Afterwards in Range H4:I6 I add the sum based on the filtered array in Range F4:F6 using these formulas:

H4 = SUMIFS($C:$C,$A:$A,$F4#)
I4 = SUMIFS($D:$D,$A:$A,$F4#)

All this works fine.

---

Now, in Range G4:G6 I want to sum up the values for each row from Range H4:I6.
So far I have implemented this formula:

=LET(
a;SUMIFS($C:$C,$A:$A,$F4#);
b;SUMIFS($D:$D,$A:$A,$F4#);
a+b)

This formula itself works. However, in my original file I have much more columns.
Therefore, I would need to chain a huge amount of SUMIFS in this formula.

Do you have any idea of a simpler way to sum up the values per row based on the flexible array in Range F4:F6?

---

NOTE: I do not want to have an all-in-one-formula!
I want to have solution with a separate formula for Range G4:G6 and a reference to the flexible array in Range F4:F6.

• excel
• excel-formula

Share Improve this question Follow edited 2 days ago Michi asked 2 days ago MichiMichi 5,45177 gold badges5555 silver badges116116 bronze badges 6

• What about =SUM(FILTER(C2:D12,A2:A12=F4)) instead of multiple SUMIFS(). – Harun24hr Commented 2 days ago
• try to use : H4 = SUMIFS(C:C,$A:$A,$F4#) | they just drag to the right.. does the new spill formula change automatically? – p._phidot_ Commented 2 days ago
• @p._phidot_ Your formula will sum from only C Column but OP summing from multiple columns. – Harun24hr Commented 2 days ago
• Firstly, I think there is a typo, the Emps in Col F should return E_01, E_02 and E_04, Secondly, the formula for F4 dont require CHOOSECOLS() as the return array used in FILTER() is only Col A therefore --> =UNIQUE(FILTER(A:A,B:B=G1)), Thid: for H4 you should use : =SUMIF($A:$A,$K4#,C:C) or =SUMIFS(C:C,$A:$A,$K4#) and copy to the right, for the last one, why not use the BYROW() --> =BYROW(H4:I6,SUM) – Mayukh Bhattacharya Commented 2 days ago
• In my last comment I have realized, even if you reference the F4# you would still need to reference the G1 cell, otherwise if any of the Emp line item is placed in other teams, then it will return FALSE POSITIVES therefore you could do this : =SUMIFS(D:D,$A:$A,$F4#,$B:$B,$G$1) and fill right and for totals: =BYROW(F4#,LAMBDA(x,SUM(IF((x=A:A)*(G1=B:B),C:D,0)))) – Mayukh Bhattacharya Commented 2 days ago

 |  Show 1 more comment

2 Answers 2

Sorted by: Reset to default 3

Think I fell down a bit of a rabbit hole with this one.
Edit.... and just noticed your I do not want to have an all-in-one-formula!.... sorry!

The formula in F3 is:

=LET(FilteredData,FILTER($A:$D,$B:$B=$G$1),
     Revenue, HSTACK(INDEX(FilteredData,,3),INDEX(FilteredData,,4)),
     AddTotals, HSTACK(BYROW(Revenue,LAMBDA(Rev,SUM(Rev))),Revenue),
     DataTable, VSTACK($A$1:$D$1,HSTACK(TAKE(FilteredData,,1),AddTotals)),
     Result, GROUPBY(TAKE(DataTable,,1),DROP(DataTable,,1),SUM,3,0),
     SUBSTITUTE(Result,"Team","Total"))  

Broken Down:

• FilteredData - filters the table to just the T_01 values.
  
• Revenue - extract just the third and fourth columns from the filtered data table.
  
• AddTotals - calculates the totals for each revenue row and then stick those totals before the Revenue columns.
  
• DataTable - Stick the Employee column infront of the AddTotals table and place the original table headers across the top.
  
• Result - sum the revenue values together grouped by the employee.
  
• Finally replace the Team header with Total.
  

You can change the 0 in the Result row to 1 if you want column totals across the bottom (or -1 for totals across the top).

With reference to the comments from Mayukh Bhattacharya below the question I have developed these two options:

Option 1:

=LET(
a,HSTACK(H4#,I4#),
b,BYROW(a,LAMBDA(r,SUM(r))),
b)

This option sill requires that I need to chain the columns in the HSTACK but it is much easier compared to chaining multiple SUMIFS.

---

Option 2:

=BYROW(F4#,LAMBDA(x,SUM(IF((x=A:A)*(G1=B:B),C:D,0))))

---

Option 3:
Solution with GROUPBY or TRIMRANGE.
See comments from Mayukh Bhattacharya below this answer.

本文标签： excelSum up values per row based on a flexible array in other columnStack Overflow