Sum Total Based On Four Different Sets of Criteria

Actaul

2016

=SUMPRODUCT(($C$16:$AU$18)*(YEAR($C$14:$AU$14)=$C$27)*($C$15:$AU$15="Actual")*($B$16:$B$18=$B28))

2017

=SUMPRODUCT(($C$16:$AU$18)*($C$14:$AU$14>=$J$23)*($C$15:$AU$15="Actual")*($B$16:$B$18=$B28))

For "Forecast", change literal to "Forecast"

OR using highlighted area in attached, replace lieral with cell containhg "Actual"/"Forecast":

=SUMPRODUCT(($C$16:$AU$18)*(YEAR($C$14:$AU$14)=$C$27)*($C$15:$AU$15=J26)*($B$16:$B$18=$B28))


I think your results are wrong as the 2017 are a mixture of Actual+Forecast id D28:D30

Hi John,

Thank you for your help and you were almost there with your formula.

I then need the one formula to take in account the “Reporting Period” date, cell C3. This date will direct the formula to sum up the totals by the “Actual” and “Forecast” rows (C15:AU15) by only summing up those "Actual" cells months that are in the past and the month of the "Reporting Period" date (C3). Then the same formula will switch gears and start summing up those "Forecast" cell months after the "Reporting Period" date (C3).

So in the attached example, the "Reporting Period" (C3) is Jun-17.
The "Project by Year" for 2016 just sums up the "Actual" from the months of Oct-16 thru. Dec-16, so the results in row "A" = $7,375, "B" = $13,950, and "C" = $23,000.
But now for the "Project by Year" for 2017 it sums up the "Actual" from the months of Jan-17 thru. Jun-17, then because the "Reporting Period" (C3) is Jun-17 the same formula then switches gears to then start summing up the "Forecast" from the months of Jul-17 thru. Dec-17, so the results in row "A" = $59,000, "B" = $60,250, and "C" = $83,950.

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

Garrett

For 2017 Forecast

=SUMPRODUCT(($C$16:$AU$18)*($C$14:$AU$14>=EOMONTH($J$23,0)+1)*($C$15:$AU$15="Forecast")*($B$16:$B$18=$B28))

You SUMS are wrong: July to Dec for "A" =37000

2017 Actual

=SUMPRODUCT(($C$16:$AU$18)*($C$14:$AU$14>=DATE(YEAR($J$23),1,1))*($C$14:$AU$14<=$J$23)*($C$15:$AU$15="Actual")*($B$16:$B$18=$B28))

Hi John,

The SUMS are correct for Jul-17 thru. Dec-17.

I need the one formula for 2017 to sum up all the "Actual" from Jan-17 thru. Jun-17, THEN after the "Reporting Period" (Jun-17) the same formula will then sum up all the "Forecast" from Jul-17 thru. Dec-17.

So, for the 2017 year, row "A" = $59,000

Please let me know if you need any more information.

Thank you again and I hope you can crack this formula!

Garrett

so you want to ADD the Actual +Forecast: yes 59000 : most confusing!!!

Hi John,

Yes, I need one formula to do all of this to come up with the results that you have highlighted in green ($59,000).

Garrett

Just combine the 2 formulae in K and L

=SUMPRODUCT(($C$16:$AU$18)*($C$14:$AU$14>=DATE(YEAR($J$23),1,1))*($C$14:$AU$14<=$J$23)*($C$15:$AU$15="Actual")*($B$16:$B$18=$B28))+SUMPRODUCT(($C$16:$AU$18)*($C$14:$AU$14>=EOMONTH($J$23,0)+1)*($C$15:$AU$15="Forecast")*($B$16:$B$18=$B28))

Hi John,

I used your formula for the 2017 year, row "A"and it came up with $63,750.

The formula also need to use the "Reporting Period" (C3) so that it knows when to automatically switch from summing up the "Actual" to summing up the "Forecast" for one total.

Thank you again and sorry if it's a little confusing, I knew it would be a tougher one.

Garrett

It came up with correct value for me.

Hey John,

Holy cow, your right, it works and you almost have it!!!

The only thing this formula needs to do is just keep the totals by year. So, when I change the "Reporting Period" to Nov-16 your current formula starts to then sum up both 2016 and 2017. I need the sum in 2016 to only contain that years total and in 2017 to only contain that years total. And that's it, you'd have it!

Garrett

If Reporting period is Nov 16 the 2016 is (logically) Oct/Nov ACTUAL plus Dec FORECAST.

And logically, 2017 is Jan-Dec FORECAST.

Requires a completely new set of formulae

2016 currently just sums 2016 actual:it does not add in any 2017 data,

Hi John,

Yes, I belive that the last formula you gave me is close enough. I'll just put it into each project year that I have (example: 2017) and then I'll just stretch the formula across the project months (example: Jan-17 thru. Dec-17) and it should work perfectly!

Thank you again for all your help!!!

Garrett