Bryan has attended:
Outlook Advanced course
Excel Intermediate course
PowerPoint Intermediate Advanced course
Excel Dashboards for Business Intelligence course
Excel Forecasting and Data Analysis course
Access Advanced course
Access Intermediate course
Excel PowerPivot course
Excel Advanced - Formulas & Functions course
Project Introduction course
Power BI Modelling, Visualisation and Publishing course
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.xlsxRE: 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
