How to easily cummulate data

Solved/Closed
DG83 Posts 38 Registration date Monday January 2, 2012 Status Member Last seen April 21, 2018 - Mar 17, 2018 at 01:49 AM
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 - Mar 17, 2018 at 03:13 PM
Hi there,

I was wondering if you guys know how to do this: got 13 weeks, each week has a figure underneath it. Let's say week 1 has 1 under it, week 2 2, week 3 3 etc.
In a drop down I would like to select eg. week 5 and I would want excel to calculate all 5 weeks combined in one cell, i.e. 1+2+3+4+5. Is this possible with a formula perhaps?

Thanks so much

2 responses

Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Mar 17, 2018 at 12:10 PM
DG83, Good afternoon.

Compare texts is always a little problem.

Suppose:
A1:M1 --> "Week 1" "Week 2"........."Week 13"
A2:M2 --> Values to be summed.

C5 --> drop down with a list of A1:M1
E5 --> Result --> Formula
=SUMPRODUCT(--(RIGHT(A1:M1,2)<=RIGHT(C5,2))*(A2:M2))

IF........... your A1:M1 is filled with 1 ; 2 ; 3 .....13
You can use this formula:
=SUMPRODUCT((A1:M1<=C5)*(A2:M2))

I did a little example for you.
https://www.sendspace.com/file/idkfqw

Please, tell us if it worked as you want.

I hope it helps.
--
Belo Horizonte, MG - Brasil.
Marcílio Lobão
DG83 Posts 38 Registration date Monday January 2, 2012 Status Member Last seen April 21, 2018
Mar 17, 2018 at 03:08 PM
Thanks a whole lot, works like a charm! Really appreciate it.
0
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Mar 17, 2018 at 03:13 PM
DG83, Good evening

Glad to have helped you.

Thanks for the feedback.
--
Belo Horizonte, MG - Brasil.
Marcílio Lobão