How to easily cummulate data [Solved/Closed]

Report
Posts
38
Registration date
Monday January 2, 2012
Status
Member
Last seen
April 21, 2018
-
Mazzaropi
Posts
1833
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
-
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 replies

Posts
1833
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
134
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

Thanks a whole lot, works like a charm! Really appreciate it.
Posts
1833
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
134
DG83, Good evening

Glad to have helped you.

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