MS Excel 2016 - Using Sumif for Year over Year Analysis - Not Working 3

[BxWill]

Using Sumif to dynamically sum the values in column C for a 6 month prior period for the current year, 2021.

Formula is correct - the sum for the desired months is displayed.

'=SUMIFS($C$7:$C$30,$A$7:$A$30,">="&DATE(YEAR(TODAY()),MONTH(TODAY())-6,1),$A$7:$A$30,"<="&DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))

Sum is correctly displayed as "21."

Conceptually, if I modify the Year(TODAY()) portion by appending "-1", as displayed below, I should sum the same six month period in the prior year.

This is not occurring. Any insight as to why?

'=SUMIFS($C$7:$C$30,$A$7:$A$30,">="&DATE(YEAR(TODAY())-1,MONTH(TODAY())-6,1),$A$7:$A$30,"<="&DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))

Sum of the numbers is incorrectly displayed as 59.


How should the formula be modified to allow for the dynamic Year over Year analysis for the same six month period?

Have ensured that the values in column A is indeed formatted as dates. Also, re-calculated by pressing F9.


Thanks in advance for any insight!

 

[combo]

Actually, in the second formula second condition you apply current year, for previous: [tt]YEAR(TODAY())-1[/tt]

You sum up values for five months only, the revised formula for last six months:
[tt]=SUMIFS($C$7:$C$30,$A$7:$A$30,">="&DATE(YEAR(TODAY()),MONTH(TODAY())-6,1),$A$7:$A$30,"<="&DATE(YEAR(TODAY()),MONTH(TODAY()),0))[/tt]

combo

 

[BxWill]

Thanks for the time and insight.

The second formula should work and the sum should be 26 not 59.

I intentionally did not include the current month. In essence, a lag is applied.

At any time during the current month, I want to review the past 6 months and compare to the same period the prior year.

It appears that the revised formula provided will display the last six months in the current year and include the current month.

Will test the revised formula.

 

[combo]

Day 0 in DATE means last day of month in formula - 1, in this case previous month.

combo

 

[BxWill]

Continuing to review and did determine that the last row of data is on row 31, not row 30.

However, the formula to display the prior years' sum is still not displaying the correct nummber of 33, not 26 as originally stated.

Note, the last number should be "7", not "4." So, by including the last row, I should receive 26 + 7, or 33.
(I had created a image of the spreadsheet prior to changing the number in the last row.)

I am now receiving 66 which is actually twice the expected amount of 33.

Will also try the use of sumproduct.

Of course, there are time intelligence functions in Power BI, DAX, etc. but unless there are some limitations with using Sumif and/or sumproduct, I will hold off on going down this route.

 

[BxWill]

Thinking this through, using the EDATE function may be the way to go due to maybe limitations with using Year(Today) and Month(Today).

Other option is to just input the actual dates and move forward.

 

[SkipVought]

Continuing to review and did determine that the last row of data is on row 31, not row 30."

You ought to be using the Structured Table feature.

1) Your formula references will be self documenting rather than the obscure A1 references AND
2) the table ranges will automatically change as the table rows expand or contract, as the case may be.

Exa:
If your Structured Table were named tBudget then...
[tt]
=SUMIFS(tBudget[Budget_Sales_Price],tBudget[Date],">="&DATE(YEAR(TODAY()),MONTH(TODAY())-6,1),tBudget[Date],"<="&DATE(YEAR(TODAY()),MONTH(TODAY()),0))
[/tt]

My additional 2 cents, I use SUMPRODUCT() almost exclusively in place of SUMIF(), SUMIFS(), COUNTIF(), COUNTIFS(). One BIG reason I use SUMPRODUCT() over this suite of aggregation formulas is that I detest putting greater than & less than in QUOTES. SUMPRODUCT() seems much MUCH more intuitive to my weak mind. You use a real expression within each inner parentheses pair.
[tt]
=SUMPRODUCT
(
(tBudget[Budget_Sales_Price])*
(tBudget[Date]>=DATE(YEAR(TODAY()),MONTH(TODAY())-6,1))*
(tBudget[Date]<=DATE(YEAR(TODAY()),MONTH(TODAY()),0))
)
[/tt]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[BxWill]

Thanks for the insight.

Initial response is that the sumproduct formula for the prior year will still include "DATE(YEAR(TODAY())-1", right?

If the Sumifs prior year formula is not currently working by using "DATE(YEAR(TODAY())-1", maybe there is another method to specify the prior year - other than the use of subtracting one year from the current year.

I use sumproduct throughout the MS Excel workbook on many other worksheets and due to the time to calculate the various worksheets, I began to use
Sumifs on the current worksheet.


Will shift to the use of sumproduct and structured tables on this worksheet and create sum product formulae for the following;

Sum Budget Sales for a rolling 6 Month period in the current year (not including current month)
Sum Budget Sales for the same 6 month period in the prior year
Average of the Budget Sales Price in the current year (not including current month)
Average of the Budget Sales Price in the prior year for the same 6 month period

 

[SkipVought]

Yes, DATE(YEAR(TODAY())-1,...

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[BxWill]

Did create formulae similar to the following;

Sum up prior 6 months in 2021 (not including the current month)

=SUMIFS($H$7:$H$186,$G$7:$G$186,"="&$P$11,$B$7:$B$186,"="&$P$10,$L$7:$L$186,">="&DATE(2020,12,1),$L$7:$L$186,"<="&DATE(2021,5,31))

Sum up the same 6 month period in 2020;

=SUMIFS($H$7:$H$186,$G$7:$G$186,"="&$P$11,$B$7:$B$186,"="&$P$10,$L$7:$L$186,">="&DATE(2019,12,1),$L$7:$L$186,"<="&DATE(2020,5,31))

Of course, now that we are in the month of July 2021, I need to change all of the formulae on the worksheet to update the 6 month period!

Any insight as to how to best accomplish?

[Bold]
The objective is to have a rolling dynamic 6 month period selected.
[/Bold]

The immediate thought is to create 12 nested IF statements in each cell where I have the original formulae using "pseudologic" such that if current month = 6, use this, if current month = 7 then use this and so on.

Sort of like

If month(Today()) = 6, SUMIFS($H$7:$H$186,$G$7:$G$186,"="&$P$11,$B$7:$B$186,"="&$P$10,$L$7:$L$186,">="&DATE(2019,12,1),$L$7:$L$186,"<="&DATE(2020,5,31)), if month (Today()) = 7, SUMIFS($H$7:$H$186,$G$7:$G$186,"="&$P$11,$B$7:$B$186,"="&$P$10,$L$7:$L$186,">="&DATE(2020,1,1),$L$7:$L$186,"<="&DATE(2020,6,30)) etc.

Note, by hardcoding the dates, I am assured of using the accurate month end date - whether 28 days in February (29 days for leap years) or 30 or 31 days in the other months.

However, I am somewhat hesitant on using 12 nested ifs with Sumifs in over 50 cells.

But, it appears that the use of Sumifs does not require the "calculation burden" on the workbook like sumproduct would.

There very well could be a VBA-based option that I am attempting to create as well.

Without the nested Ifs and the VBA-based option, maybe the use of time intelligence funtionality using business intelligence, DAX, etc. is the optimal solution.

Thanks in advance for any insight as to the best preferred method.

 

[BxWill]

Just had a thought - It is maybe best to just use four cell references or four dynamic range names within each formula instead of the hardcoded dates...