# cumulative sum based on range

#### wongsol

##### New Member
Hello:

I'll try to explain this the best way I can.

I've been asked to do a resource matrix based on complexity of work and duration of a project.

Complexity would be values of 0-5. Duration would be based on a start date and an end date. I think the WEEKNUM function may work out here [e.g. week 9 (start) week 45 (end); the difference would be 36 (weeks)].

I would need to take the start week and populate the count from week 9 all the way through to week 45..run a loop through the data and continue to populate each cell for each week that there is activity (or duration/difference between the two dates).

So if there is a project that starts week 1 and ends week 5 and another project that starts week 2 and ends week 4...the table should look like this...

For complexity 0 and PM Joe; week1=1, week2=2 week3=2, week4=2, week5=1.

Any help would be greatly appreciated, I'm going bald

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying
And ... what is the question?
Before going bald could you explain the problem in a "for dummies" manner? Something like: "I have this data organised like this and I need to get this kind of result by processing the data in the following way"... Maybe you have all data in a sheet and you want the results in another (or several other) sheet(s), or maybe you need a macro with a dialogue, or a wizard, or a set of formulas, or... who knows (?). I understand that for complexity 0 the table should look like week1=1 and so on, but I don't understand who's PM Joe (is PM standing for "Prime Minister", or is it "Post Meridian", not mentioning "Post Mortem"... ?). The only thing I'm sure I understood from your explanation above is that 45-9=36 (speaking of weeks, of course) - still looking for that "cumulative sum based on range" and still can't find it except in the title of your post (even without a mirror at hand I have the same feeling about baldness... not sure if this can be of any help)

I have this organized like this...

A column that specifies a PM (Project Manager), column that specifies complexity (0-5), a column that specifies the start work week and another column that specifies end work week (9 and 45 respectively).

So I want to loop through this data sheet and find (for example) Complexity 1; PM "Joe"; look at the start week and end week (9-45 resp) and put this in to another sheet that would reflect the "week1=1, week2=2 week3=2, week4=2, week5=1" cumulative total...

..so the results page would have Week 1 (B1), 2 (C1), 3(D1) etc across row 1 as the Header..
Column A would have 0-5 (A2,A3,A4,A5 etc..); the other array would find PM="Joe".

So with criteria for the search complexity 0; PM=Joe, from the source page = 2 projects that match one with start week = 1, end week = 5 and the other match start week = 2, end week =5 my result page should look like B2=1, C2 =2, D2=2, E2=2, F2=2 with A2 as my indicator for "0" complexity and B1-B53 values as 1-52 (indicating the week number)...so I need to know how to accomplish this result.

I hope I've explained it well enough.

SW

I'm not sure I understood exactly what you want, but here's a sample of what I could understand.
In Sheet1 there is a table like this:

Excel Workbook
ABCD
1PMComplexityStart WeekEnd Week
2Joe015
3Anna016
4George2312
5Anna1511
6Joe025
7John3124
8George2415
9Joe126
10John4230
11Joe149
Sheet1

</body></html>

In Sheet2 there is this table for PM Joe:

Excel Workbook
ABCDEFGHIJK
1Joe12345678910
201222200000
310112221110
420000000000
530000000000
640000000000
750000000000
Sheet2

</body></html>

There can be more tables in Sheet2 (for each PM), or there can be more sheets with tables like this (one sheet/PM), but anyway, the formula written in cell B2 of this example can be copied across the whole Joe's table and of course the week number can go up to 52.

Hope I could be of some help.

Thank you gecs.

This is awesome! Exactly what I was looking for!

Thank you so much and I apologize for my lack of explaination and confusion.

Thank you again!!

You're welcome. I also have to apologize for wasting forum space due to my lack of experience with Excel jenie which led to the unnecessary long list of formulas to be displayed.

Sometimes confusion leads to unexpected good results if we find the patience to clear it up a bit. Anyway it was fun writing my first reply to this topic. I hope you didn't take it otherwise than a joke about "going bald"

No offence taken...I have a full set of hair, getting the greys but it was bound to come.

Thanks again for the quick response and aid.

SW

Replies
2
Views
224
Replies
5
Views
140
Replies
0
Views
261
Replies
5
Views
346
Replies
0
Views
181

1,196,409
Messages
6,015,103
Members
441,870
Latest member
kojack

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back