0

In Excel, I have a table of historical value data of different items in multiple groups of items. As a new item's record entered, the old value of that item has to be abandoned and the total values of items in the group must be recalculated based on the new item's value. In each group, based on group name and date specified, just the last record of each item is embraced; every item has different last date less than the date specified. I want to find the sum of items' values in each group based on group name and specific date, how do i do this?

image of the sample table(input and desired output)

As displayed in the image, data are grouped in two groups, G1 and G2,... In each row I need to calculate the total value of items in its corresponding group that already been entered until that date. At the right of the table, I filtered the desired records based on the Date and Group fields, then the output will be the sum of the Values displayed in green frame. In date D3, as a new value entered for Item i11, the group total value recalculated based on this new value. Also in the table, the total value of each group in each row is displayed in column [Sum of Group], also by formula in the last two columns.

Data:

Row Group Item Date Value Sum of Group (expected output)
1 G1 i11 D1 10 30
2 G1 i12 D1 20 30
3 G1 i13 D2 30 60
4 G1 i11 D3 40 90
5 G1 i14 D4 50 140
6 G1 i13 D5 60 170
7 G1 i13 D6 70 190
8 G1 i13 D6 80 190
9 G1 i12 D7 90 260
10 G1 i14 D8 100 310
11 G2 i21 D8 110 110
12 G1 i13 D9 120 350
13 G2 i22 D9 130 270
14 G2 i21 D9 140 270
Improve this question
1

2 Answers 2

Reset to default
0

You tagged Excel 2010.

I managed to get a solution in Office 365:

=LET(f,FILTER(HSTACK([Item],ROW([Item])-1),([Group]=[@Group])*([Date]<=[@Date])),
     i,TAKE(f,,1),
     r,DROP(f,,1),
SUM(INDEX([Value],FILTER(r,MMULT((TOROW(i)=i)*(TOROW(r)>=r),SEQUENCE(ROWS(f))^0)=1))))

enter image description here

The formula first filters all items i of the same group and same date or smaller - together with it's row references (minus 1), since the table starts at row 2,which is the first row, so ROW(2)-1 = 1) r.

MMULT is used to check if there are any duplicate items within the filter result. Only the last found item of i is returned as TRUE.

This is used as a FILTER condition, to remove previous duplicates and only return the row numbers of the last found values of i.

These row numbers are used on the INDEXed Item column and these returned values are SUMmed.

or when you're on Beta Chanel Insiders:

=LET(f,FILTER(HSTACK(Table2[Item],ROW(Table2[Item])),(Table2[Group]=Table2[@Group])*(Table2[Date]<=Table2[@Date])),SUM(INDEX(E:E,DROP(GROUPBY(TAKE(f,,1),DROP(f,,1),MAX,,0),,1))))

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Thank you. I could verify the algorithm, but as I work with Excel-2021, I can't test it.
I think this is compatible: =LET(f,FILTER(CHOOSE({1,2},[Item],ROW([Item])-1),([Group]=[@Group])*([Date]<=[@Date])),i,INDEX(f,,1),r,INDEX(f,,2),SUM(INDEX([Value],FILTER(r,MMULT((TOROW(i)=i)*(TOROW(r)>=r),SEQUENCE(ROWS(f))^0)=1))))
TOROW is an Office 365 function, how could its function be implemented in Excel 2021?
Oh, my bad, replace it with TRANSPOSE =LET(f,FILTER(CHOOSE({1,2},[Item],ROW([Item])-1),([Group]=[@Group])*([Date]<=[@Date])),i,INDEX(f,,1),r,INDEX(f,,2),SUM(INDEX([Value],FILTER(r,MMULT((TRANSPOSE(i)=i)*(TRANSPOSE(r)>=r),SEQUENCE(ROWS(f))^0)=1))))
This is correct and excellent. It will be perfect, if please explain how it works, or give some references on the subject. Thank you very much.
|
0

This formula spills the results in my sheet.

EDIT: Changed SEQUENCE(14) to SEQUENCE(ROWS(data)) in order to dynamically return the number of rows in a sequence. The screenshot still shows the old version SEQUENCE(14).

=LET(data,A2:E15,DROP(REDUCE("",SEQUENCE(ROWS(data)),LAMBDA(x,y,VSTACK(x, LET( z,FILTER(HSTACK(CHOOSECOLS(data,1),CHOOSECOLS(data,3)),(CHOOSECOLS(data,2)=INDEX(CHOOSECOLS(data,2),y))*(CHOOSECOLS(data,4)<=INDEX(CHOOSECOLS(data,4),y))), u,SORTBY(z,DROP(z,,1),-1,TAKE(z,,1),-1), v,XLOOKUP(UNIQUE(DROP(u,,1)),DROP(u,,1),TAKE(u,,1)), SUM(INDEX(CHOOSECOLS(data,5),v)))))) ,1) )

How to find sum of a group of items' values based on the last value of each item?

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.