| 0 | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Data List | Filter | ||||
| 2 | date | prod. | val. | 2022-12-31 | ||
| 3 | 2022-05-10 | P1 | 15 | |||
| 4 | 2023-01-15 | P1 | 18 | |||
| 5 | 2022-08-12 | P2 | 30 | |||
| 6 | 2022-09-15 | P3 | 50 | Result | ||
| 7 | 2023-06-15 | P3 | 20 | prod. | val. | |
| 8 | 2023-06-18 | P1 | 80 | P1 | 15 | |
| 9 | 2024-05-17 | P4 | 25 | P2 | 225 | |
| 10 | 2023-08-19 | P5 | 85 | P3 | 60 | |
| 11 | 2022-09-30 | P3 | 70 | |||
| 12 | 2024-04-25 | P3 | 32 | |||
| 13 | 2025-08-29 | P1 | 45 | |||
| 14 | 2022-03-25 | P2 | 60 | |||
| 15 | 2022-03-26 | P2 | 85 | |||
| 16 | 2024-05-20 | P3 | 90 | |||
| 17 | 2024-06-03 | P4 | 15 | |||
| 18 | 2024-07-30 | P4 | 26 |
In Cell E8 I have implemented the below formula to filter the Columns A:C based on the criteria in Cell E2.
=LET(
a,FILTER(A2:C18,A2:A18<=E2),
b,CHOOSECOLS(a,2),
c,CHOOSECOLS(a,3),
d,HSTACK(b,c),
d)
All this works fine.
However, the issue is that
- the
prod.are not listed unique and - the
val.is not summed up to each uniqueprod.
The first issue is easy to solve with UNIQUE function but I have no clue how to solve the second issue with the sum.
I know in the latest Excel version there is the GROUPBY function which solves this issue.
However, I do not have this function yet available in my Excel. I am using Office365 (Version 2410).
Do you know an alternative way to make it work?
Try the following formula-
=LET(
x,UNIQUE(FILTER(B3:B18,A3:A18<=E2)),
y,SUMIFS(C3:C18,A3:A18,"<="&E2,B3:B18,x),
HSTACK(x,y))
Using GROUPBY() function.
=GROUPBY(B3:B18,C3:C18,SUM,,,,A3:A18<=E2)