Sum of range in column

New Contributor

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.

Day1Case wanted output 
17   
20 Intervaltotal cases
35  day 1-419
47 day 5-9102
512 day 1019
643 day 11-17250
76 day 18-20136
816   
925   
1019   
1113   
1238   
1339   
1472   
1524   
1619   
1745   
1845   
1946   
2045   
4 Replies

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

 

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

@odko221100 

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

 

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

image.png

Some pretty noticeable differences?