Jul 09 2022 05:53 PM
Hi, could anyone please help me to solve following problem?
I have a set of data as shown in left side and want to have result as shown in right side. I tried to use sumproduct but still cant find good solution. Thanks in advance.
Day1 | Case | wanted output | ||
1 | 7 | |||
2 | 0 | Interval | total cases | |
3 | 5 | day 1-4 | 19 | |
4 | 7 | day 5-9 | 102 | |
5 | 12 | day 10 | 19 | |
6 | 43 | day 11-17 | 250 | |
7 | 6 | day 18-20 | 136 | |
8 | 16 | |||
9 | 25 | |||
10 | 19 | |||
11 | 13 | |||
12 | 38 | |||
13 | 39 | |||
14 | 72 | |||
15 | 24 | |||
16 | 19 | |||
17 | 45 | |||
18 | 45 | |||
19 | 46 | |||
20 | 45 |
Jul 09 2022 09:03 PM
@odko221100 Two possible solution. One with SUMIFS. Another with SUMPRODUCT. See which one suits you best. Examples in the attached file.
Jul 10 2022 04:24 AM
@Riny_van_Eekelen has covered the normal database approach of running through the entire list of values filtering out those that do not match the given criteria. SUMIFS is extremely fast in terms of its execution, SUMPRODUCT less so.
With 365 it is also possible to pass the parameters to a Lambda function and build the bounded calculations one at a time.
= MAP(To, From, LAMBDA(t,f,
SUM(INDEX(Case, t) : INDEX(Case, f))
))
will build the individual ranges and sum them. It is also passible to go for something more complicated and build an array sum for each calculation using SEQUENCE.
= MAP(From,To,SumSeqλ(Case))
SumSeqλ
= LAMBDA(rng,
LAMBDA(f,t,
SUM(INDEX(rng, SEQUENCE(1+t-f,,f)))
)
)
Perhaps I should build a dataset with 100,000 values and perform some timing runs!
Jul 10 2022 07:29 AM
These were the timings in milli-seconds for 2500 evaluations (10000 data rows)
Some pretty noticeable differences?