New Contributor

# Sum of range in column

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
4 Replies

# Re: Sum of range in column

@odko221100 Two possible solution. One with SUMIFS. Another with SUMPRODUCT. See which one suits you best. Examples in the attached file.

# Re: Sum of range in column

@Riny_van_Eekelen Oh, Thank you very much!!!!

# Re: Sum of range in column

@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!

# Re: Sum of range in column

These were the timings in milli-seconds for 2500 evaluations (10000 data rows)

Some pretty noticeable differences?