Perform Rolling Sum Based on Another Column's Value in Excel Table

Question

I'm looking to perform a rolling sum for an excel table based on the value of a column.

I want the sum only up to the current row (not sum of all rows that have the same value)

I've made a simple example to explain:

I want to use table references in a formula to get the item-wise rolling sum for the first 2 columns in the table below. 'Item' and 'Value' are known ; 'Item_Wise_Rolling_Sum' is to be calculated

Item	Value	Item_Wise_Rolling_Sum
a	1	1
a	2	3
b	10	10
b	5	15
b	2	17
c	6	6

I tried to use the following formula to no avail:

=SUMIFS(MyTable[[#Headers],[Value]]:[@Value], [Item], [@Item])

The idea was to calculate sum uptill the current row only, not for all rows below.

Thank you in advance.

Answer 1

For older versions try using SUMIFS()

---

=SUMIFS(B$2:B2,A$2:A2,A2)

---

Or, In MS365 try using MAP()

---

=MAP(A2:A7,B2:B7,LAMBDA(x,y,SUM((A2:x=x)*B2:y)))

---

Or, using SUMIF() with Structured References

---

=SUMIF(MyTable[[#Headers],[Item]]:[@Item],[@Item],MyTable[[#Headers],[Value]]:[@Value])

---

With SUMIFS() need to change the ranges correctly as per the parameters,

=SUMIFS(MyTable[[#Headers],[Value]]:[@Value],MyTable[[#Headers],[Item]]:[@Item],[@Item])

---

Another alternative way of anchoring and using SUMIFS() for running total in Excel Tables: (Comparatively Shorter Method)

---

=SUMIFS(INDEX([Value],1):[@Value],INDEX([Item],1):[@Item],[@Item])

---

As commented by JvdV Sir, alternative approach using MAP() if in future you think of not using Structured References then try the below.

=MAP(A2:A7,B2:B7,LAMBDA(x,y,SUMIF(A2:x,x,B2:y)))

---

Answer 2

I'm not sure if another answer is necessary, but you can also just use the current row:

=SUM([Value]*(ROW()>=ROW([Value]))*([Item]=[@Item]))

Answer 3

Using table references: =SUMIF(MyTable[[#Headers],[Item]]:[@Item],[@Item],MyTable[[#Headers],[Value]]:[@Value])