How to easily cummulate data [Solved/Closed]

Report
Posts
38
Registration date
Monday January 2, 2012
Status
Member
Last seen
April 21, 2018
-
Posts
1834
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
September 24, 2020
-
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
1834
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
September 24, 2020
135
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
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
1834
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
September 24, 2020
135
DG83, Good evening

Glad to have helped you.

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