gantt showing cumulative total

AI Training: For Busy Decision Makers & Professionals Book now

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Gantt showing cumulative total vs daily in columns | Excel forum

Gantt showing cumulative total vs daily in columns | Excel forum

resolvedResolved · Medium Priority · Version 2016

Gantt showing cumulative total vs daily in columns

We have a simple Gantt that shows start and end dates along with number of seats assigned. I need to show how many total seats assigned each day for overlapping dates. The best I could do is the formula below but it keeps running total vs a daily total and adds everything from right to left. (left to right 20, 50 100 seats and shows 50, 150, 170). It’s almost as hard to describe this question in words and hope it is somewhat clear what I am looing for.
______
Cell G7 is 31 Mar and L7 is 100 h7 is 31 Mar (170)
=SUMIFS($L7:$L$282,$H7:$H$282,">="&Q5,$G7:$G282,"<="&$BQ5)
__________
Cell G21 is 1 Apr and L21 is 50 H21 is 5 Apr (150)
=SUMIFS($L7:$L$282,$H7:$H$282,">="&AT5,$G7:$G282,"<="&$BQ5)
____
Cell G39 is 28 Feb and L39 is 20 H39 is 2 Mar (50)
=SUMIFS($L7:$L$282,$H7:$H$282,">="&AY5,$G7:$G282,"<="&$BQ5)

Thanks
Bryan

RE: Gantt showing cumulative total vs daily in columns

Hi Bryan,

Thank you for the forum question.

Is it possible for you to send the worksheet. You can copy the part you want me to help you with and paste it in a new file, if you have information in your file, you cannot share with me.

You can send it to:
info@stl-training.co.uk

and please write in the email, that the file is for Jens.

Thanks


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Gantt showing cumulative total vs daily in columns

Hi Bryan,

Please find attached file.

I am sorry I have messed up your file a little bit but if you look in row 2, my Sumifs return the right result. You are using the table tool. I have just reference the table instead of the cell references.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Attached files...

Copy of GCMC_Resources.xlsx

RE: Gantt showing cumulative total vs daily in columns

Hi Bryan,

I have had time to look at your file.

It is correct understood, that if you have two events both with 20 seats, both with a start date 28 Feb to 3 March and no other events in this period, you want the Sumifs to display 40 for each day (28 Feb to 3 March) and if you have an event starting from 2 March with 10 seats.

You want the Sumif to display 40 for the 28 Feb and 1 March and 50 for 2 March and 3 March.

I hope my question makes sense


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

 

Training courses

 

Training information:

See also:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Customize the toolbar in Excel 2010

You can create your own toolbar which contains your favourite or most used tools. This will make using Excel much more efficient. To do this, you need to click on View, then select Customize Quick access Toolbars and then select Customize. A list of tools will then appear on the screen of which you can add or remove them as you please.

View all Excel hints and tips


Server loaded in 0.14 secs.