Sum Total Based On Four Different Sets of Criteria

I need a formula that will sum each “Project by Year” (E11:E13 & F11:F13) of both “Actual” Cost and “Forecast” Cost for each Month (H11:AZ13).

The formula would use the “Project (PTD)”, “Start” (C7) and “End” (C8) dates as the one set of two dates as 1st criteria, the “Project by Year” dates (E7:E8 & F7:F8) as the second set of two dates as the 2nd criteria, the “Project by Month” dates (H7:AZ8) as the third set of two dates as the 3rd criteria, and lastly the “Project by Month” cost descriptions for “Actual” Cost and “Forecast” Cost (H10:AZ10) as the 4th criteria.

So for the “Actual” Cost, too correctly sum each month into their separate 2013 and 2014 yearly column, the formulas follows the 1st criteria by only summing those totals between the “Project (PTD)”, “Start” date (C7) and the “End” date (C8).

Next, the formula would follow the 2nd criteria by only summing those totals between the “Project by Year”, 2013 “Start” date (E7) and the “End” date (E8) or the 2014 “Start” date (F7) and the “End” date (F8).

Next, the formula would follow the 3rd criteria by only summing those totals between the “Project by Month”, monthly set of “Start” dates (H7:J7, K7:M7, etc…) and “End” dates (H8:J8, K8:M8, etc…) for every month.

Lastly, the formula would follow the 4th criteria by only summing those totals between the “Project by Month”, under the monthly set of “Start” dates (H7:J7, K7:M7, etc…) and “End” dates (H8:J8, K8:M8, etc…) that contain the “Actual” Cost and “Forecast” Cost column titles for every set of months (H10:J10, K10:M10, etc…).

The formula contained between the “Project by Year” 2013 and 2014 columns (E11:F13) would only sum those months that “Start” on 10/01/2013 (C7) and would “End” on 09/31/2014, but only the “Project by Month”, “Actual” Cost would be totaled respectively into their 2013 and 2014 project year columns.

Likewise, to correctly sum the “Forecast” Cost for each month into their separate 2013 and 2014 yearly columns, the formulas contained between the “Project by Year” 2013 and 2014 columns would only sum those months that come after the “End” date (C8) after 09/31/2014, but only the “Project by Month”, “Forecast” Cost would be totaled respectively into their 2013 and 2014 project year columns.

Thank you for your help and please let me know if you need any additional information.

Sum Total Between Multiple Date Criteria.xlsx

Garrett

So this is another version of your other post???

You may also want to check your input data, for example cell C8 shows the month of September with 31 days ...

Hi Ursul,

No, this is a second (and final) formula I need help on.

On my last post for the first formula I needed help on, you were precisely correct in your formula. I was pasting it into my spreadsheet example template incorrectly, so thank you for updating my template with your working formula for me! =)

So, if you could use your Excel Jedi Master skills on this last formula, I'd very much appreciate it! :-)

Garrett

Hi Ursul,

You are correct, I slight miss on my part on the (cell C8) date.

Please see the corrected spreadsheet example template below.

Sum Total Between Multiple Date Criteria (Rev.1).xlsx

Garrett

OK I'll have a look at it but the layout my make life a little tricky and an even better Excel Master Jedi may be required to solve this one!

Use the Force!!! =)

You can do it man, I have faith in you!

Hey Garrett, if the other post is SOLVED then please mark it as such and if you like you can click on the *Add reputation at the bottom left of my post.

Cheers,

Hi Ursul,

I already marked my last post you helped my on as SOLVED and I have already added to your reputation as an "Excel Jedi Master!!!"

I did more like the solution you provided me with, I thought it was AWESOME!

Garrett

Is there anyone else out there in the Excel Forum that can solve this formula, if Ursul (above) is finding difficulty in solving it???

Garrett

So have a look at this revised version:

SUMPRODUCT - Between Multiple Criteria - 01.xlsx

May the Force be with you!

Hi Ursul,

In your revised version above, the formula is close to what I need.

The "Project (PTD)", "Start" date (C7) and "End" date (C8) control the range of time on the project the "Actual" Cost are applied to the project and are in the past. The "Forecast" Cost are taken in account after the "Project (PTD)", "End" date (C8) and into the future when ever the project is scheduled to end (this project end date is unimportant of this formula).

So, the total 2014 project year is made up of two parts of "Actual" Cost and "Forecast" Cost and I'll use just those totals related to WBS "210". In 2014 the "Actual" Cost are from 01/01/2014 (or Jan-14) to 09/30/2014 (or Sep-14) and sub-total $33,800. In the same 2014 year, where the "Actual" Cost" end on 09/30/2014 (or Sep-14), the "Forecast" Cost begin totaling up between 10/01/2014 (or Oct-14) to 12/31/2014 (or Dec-14) and sub-total $19,750. The 2014 year total between the "Actual" Cost and "Forecast" Cost is $53,550.

I hope this helps Obi-Wan Kenobi. =)

Garrett

The attached file, "Sum Total Between Multiple Date Criteria (Rev.1)b" just above by Humdingaling has almost the exact formula I need and is so close.

The only and last thing I need this formula to do is in the same project year (in this case it's 2014), have the ability to automatically switch between summing from the 2014 "Actual" Cost to the 2014 "Forecast" Cost (colored in purple) based on the "Project (PTD)" End date (C8).

So for example, if we change the "Project (PTD)" End date (C8) from the current 02/01/2014 to 09/30/2014 the formula contained in the "Project by Year" reflects this change and as a result changes the 2014 project year output (F11:F13) to redirect this formula to now sum (respective to the 1, 2, and 3 rows) all "Actual" Cost in 2014 from 01/01/2014 (or Jan-14) to 09/30/2014 (or Sep-14) equaling a sub-total of $33,800. Next, any date after the "Project (PTD)" End date (C8) is in the future, so this same formula then switches to summing only the "Forecast" Cost in 2014 from 10/01/2014 (or Oct-14) to 12/31/2014 (or Dec-14) equaling the sub-total of $28,750. So, the final output of the formula would result in a combined 2014 total of $62,550.

Just a note on the relevance for my last formula request above, between the "Project (PTD)", "Start" date (C7) and "End" date (C8) control the range of time on the project the "Actual" Cost are applied to the project and are those project costs that are real and that are in the past. The "Forecast" Cost are taken in account after the "Project (PTD)", "End" date (C8) and since the actual work and costs don't exist yet these columns are lefty blank, but instead we can forecast future costs as to what we think the project is going to spend up to when ever the project is scheduled to be completed.

I hope this helps better clarify the single last change this formula would need to work.

Thank you again for everyone's help and time on this as I can't thank you enough...

Garrett

Hey Humdingaling,

I'll be dam you did it!!!

Your incredible!

Thank you for all your help and time putting together the monster formula the works perfectly!

Garrett