How to calculate the sum of values in cells which contain formulas

[steve@stanley]

Hi, hopefully someone can help. I’m a 2007 user but some of my colleagues use 2010 so this needs to work in both versions.
I have a worksheet that is to be used much like a mini version of MS project: a column of jobs listed by site name (A), a column of start dates (B) and a column of finish dates (C). This list could run to hundreds of rows eventually. Across the top is a row (1) of the calendar dates. I already have a formula entered so that when I put in a start and finish date in (say) B2 and C2, then a 1 will be entered into the corresponding cells under the relevant dates further along the same row.
All the above works fine so far, but I want to be able to add the 1’s for each date (so I know how many people I need to have working on any given date).
I’ve tried various different formulas, sum, sumif, subtotal, countif, etc but they all seem to count the number of formulas and not the output of the formula. I don’t want to have to Copy, Paste Values to enable the count to be done.
Several people may end up using this and I’d like to protect it eventually so formulas cannot be changed. The only thing I want people to do is enter data in columns A, B and C with all other columns protected, letting the formulas do the rest.

Attached is a sample worksheet which shows a basic version of what I am after - example: I need cell D14 to show 2, not 11 and E14 should show 3.

Any help appreciated, thanks

[Bob Phillips]

Change your Gantt formula to

=IF(AND(D$1>=$B2,D$1<=$C2),1,"")

and then you can use

=SUMIF(D$2:D$12,1)

[Bob Phillips]

You could also make it more Gantt like by applying conditional formatting to the range D2:AG12, setting the font and fill formats to the same colour on a cell value of 1.

[Russell Dawson]

Or

in AH2 =COUNTIF(D2:AG2,1) and copy down

To protect the calc areas - highlight D1:AH14 < r click format cells < protection - uncheck locked < ok

Then you need to select Review tab in ribbon < protect sheet < uncheck select locked cells < insert password < ok

[steve@stanley]

Thanks Bob - that works just as I'd asked for.
Can I go a stage further and ask how i'd calculate sub totals if I were to filter column A for specific areas / regions and have the subtotal showing across row 14?
with the solution you gave above, [=SUMIF(D$2:D$12,1)] if I filter col A, the figure in row 14 stays as it was.

thanks also for the tip on CF - I'll add that feature later

[steve@stanley]

Russell,
Thanks for the protection help.

I'm looking for a subtotal calculation under the gantt section for each date column, so the total would be across row 14. see my reply to Bob's original solution
originally I asked for a total which Bob has helped with, but I now find I need a subtotal to be calculated when col A is filtered

[NBVC]

Try, in D14:

=SUMPRODUCT(SUBTOTAL(9,OFFSET(D$2:D$12,ROW(D$2:D$13)-MIN(ROW(D$2:D$12)),,1)))

copied across.

[steve@stanley]

I have managed to get a solution to this before NVBC joined in (but thank you anyway - I will try your solution.
The attached file (Project Dates4a) has been manipulated somewhat: CF has been utilized, I've moved the totals row to the top so it is always visible, added a new 'region' column and additional regional totals also across the top.
I'm happy with this so big thanks to all who chipped in with help. Hopefully it woill helpsomeone else too.

thanks,
Steve

[Bob Phillips]

Steve,

I see you took half my suggestion on CF, but if you make the font the same as the fill colour, it looks much nicer

Also, why do you test the region twice in your SUMIFS formula, once is plenty.

As you now have regional totals, you might as well change the SUMIF formula in row 6 to a simple SUM of the above 4 cells.

You can use =LEFT(TEXT(E8,"ddd"),1) in E7:BI7 to get the day letter, helps in case a project starts on a Wed say.

Finally, as you have the green cells unlocked, all others locked, why don't you add worksheet protection, then they can't enter data anywhere but the green cells.

[steve@stanley]

Bob,
Appreciate the further reply and help. to answer your points:

Not sure what you mean by the first point - if the font is the same colour as the fill, surely you won't be able to read the content of the cell? Unless you mean the same colour as the border?

I hadn't realised I was testing the region twice. I tweaked a previously used formula from another project and it worked so I left as it was! I have now changed it to your simpler version.

I have also changed to the simpler formula on row 6.

also used your formula for the day letter (instead of manually emtering). this is very useful and I'll definately use that one again, thank you.

The protection I knew about and intend to use do it as you have stated, but had left off for now. I'll fully protect once it is issued out for use by others.

Thanks,
Steve

[Bob Phillips]

[QUOTE=steve@stanley;2852480Not sure what you mean by the first point - if the font is the same colour as the fill, surely you won't be able to read the content of the cell? Unless you mean the same colour as the border?[/QUOTE]

That is exactly the point Steve, you don't want to see the value. The fill colour is enough to tell the viewer that that date is used. Personally, I wouldn't bother with a formula in the cell, just use a formula that tests the dates in the CF.

Glad the other bits were useful.