cumulative sum based on range


New Member
Oct 17, 2007

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 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)
Upvote 0
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... 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) I need to know how to accomplish this result.

I hope I've explained it well enough.

Upvote 0
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:

<html><head><title>Excel Jeanie HTML</title></head><body>
Excel Workbook
1PMComplexityStart WeekEnd Week


In Sheet2 there is this table for PM Joe:

<html><head><title>Excel Jeanie HTML</title></head><body>
Excel Workbook


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.
Upvote 0
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!!
Upvote 0
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" :)
Upvote 0
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.

Upvote 0

Forum statistics

Latest member

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
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
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