Combining Formulas

Archie123456

Can someone please help me combine these two formulas i cant figure it out.

=SUMIFS({Dev Fee}, {Site Status (Dashboard)}, OR(CONTAINS("Live", @cell), CONTAINS("Completed", @cell), CONTAINS("Exchanged", @cell), CONTAINS("HOT's Signed", @cell)

{Forecast Shovel Ready}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = IF(TODAY() >= DATE(YEAR(TODAY() + 365), 1, 1), YEAR(TODAY()), YEAR(TODAY()) - 1)))

Jason P

Hi @Archie123456

Friends at Chat suggest. =SUMIFS({Dev Fee}, {Site Status (Dashboard)}, OR(CONTAINS("Live", @cell), CONTAINS("Completed", @cell), CONTAINS("Exchanged", @cell), CONTAINS("HOT's Signed", @cell)), {Forecast Shovel Ready}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = IF(TODAY() >= DATE(YEAR(TODAY() + 365), 1, 1), YEAR(TODAY()), YEAR(TODAY()) - 1)))

Archie123456

Great, ontop of this does this formula work for the next 12 rolling months for example July 24, Aug 24, Sep 24,Oct 24, Nov 24, Dec 24, Jan 25 all the way to Jun 25.

=SUMIFS({Dev Fee}, {Site Status (Dashboard)}, OR(CONTAINS("Live", @cell), CONTAINS("Completed", @cell), CONTAINS("Exchanged", @cell), CONTAINS("HOT's Signed", @cell)), {Forecast Shovel Ready}, AND(IFERROR(MONTH(@cell), 0) = 12, IFERROR(YEAR(@cell), 0) = IF(TODAY() >= DATE(YEAR(TODAY() + 365), 12, 1), YEAR(TODAY()), YEAR(TODAY()) - 1)))

Paul Newcome

Your formula will only grab "last December" based on today's date. If you need a rolling 12 months, how exactly would that look? All the way down to "today" or would you want from the 1st of the current month to the last day of the previous month but next year?

Archie123456

I want it to show the next 12 months past this month starting from the first of the month onwards so 01/07/24 - 31/06/25, and this continue every month as an advance for the 12 month or a 12 month forecast.

Paul Newcome

In that case, your date criteria would be more like this:

AND(@cell >= DATE(YEAR(TODAY()), MONTH(TODAY()), 1), @cell <= DATE(YEAR(TODAY()) + 1, MONTH(TODAY()), 1) - 1)

Archie123456

How would i do this for the rest of the months in the year?

Paul Newcome

I'm not sure I follow. Are you wanting separate formulas for each month? The above {Date Range} criteria set will count for everything for "…the next 12 months past this month starting from the first of the month onwards so 01/07/24 - 31/06/25…" all in one count based on today's month (automatically updating as the months progress).

Archie123456

Hi Paul,

I want individual formulas so it will count for each rolling month for example the same time frame but when July is over it will then forecast for July 25. Then next year it will do July 26 forecast. And on and on.

Paul Newcome

Ah. Ok. I misunderstood what you needed. I would suggest inserting a text/number column with the numbers -12 through 0 entered. Then you can use this EDATE solution to populate the dates going down a date type column (or up if you prefer):

=IFERROR(IFERROR(DATE(YEAR(TODAY()) + ROUNDDOWN((MONTH(TODAY()) + [Number of Months]@row) / 12, 0) + IF(IF(MOD(MONTH(TODAY()) + [Number of Months]@row, 12) = 0, 12, MOD(MONTH(TODAY()) + [Number of Months]@row, 12)) = 12, -1) - IF(AND(ABS([Number of Months]@row) - MONTH(TODAY()) <> 12, [Number of Months]@row < 0, ABS([Number of Months]@row) > MONTH(TODAY())), 1, 0), IF(MOD(MONTH(TODAY()) + [Number of Months]@row, 12) = 0, 12, MOD(MONTH(TODAY()) + [Number of Months]@row, 12)), 1), DATE(IF(MONTH(TODAY()) - ABS([Number of Months]@row) < 1, YEAR(TODAY()) - 1, YEAR(TODAY())), IF(MONTH(TODAY()) - ABS([Number of Months]@row) < 1, MONTH(TODAY()) + (12 - ABS([Number of Months]@row)), MONTH(TODAY()) - ABS([Number of Months]@row)), 1)), "")

Last but not least would be a text/number column with your SUMIFS. The criteria for the {Date Range} would be:

AND(IFERROR(MONTH(@cell), 0) = MONTH(EDATE@row), IFERROR(YEAR(@cell), 0) = YEAR(EDATE@row))

Archie123456

Hi Paul,

Apologies I think it have just confused things more. I want a rolling 12 months as shown in the image below

Paul Newcome

Oh. Ok. That's definitely different than what I pictured when reading "rolling". Try this instead:

Put the numbers 1 through 12 in the text/number column (called "Number" in this example) and then use this column formula in a date type column…

=DATE(YEAR(TODAY()) - IF(TODAY()) <= DATE(YEAR(TODAY()), Number@row, 1), 1, 0), Number@row, 1)

Then you can use your method of choice to output "Mmm YY" in a [Labels] column if needed

Archie123456

Would you be able to add this to

=SUMIFS({Dev Fee}, {CPO (Primary)}, <>"Zood Charge (Not SDCL)", {Site Status (Dashboard)}, OR(CONTAINS("Live", @cell), CONTAINS("Completed", @cell), CONTAINS("Exchanged", @cell), CONTAINS("HOT's Signed", @cell)))

Paul Newcome

Add how?

Archie123456

Hi Paul,

Add the formula you made to mine above, i cant seem to combine the two together.

Paul Newcome

The formula I provided would go in a date type column. Your SUMIFS would then include a cross sheet reference to the date column in your source sheet and then compare the month and year to the date column that houses the above formula.

…….., {Date Range}, AND(IFERROR(YEAR(@cell), 0) = YEAR([Date Column]@row), IFERROR(MONTH(@cell), 0) = MONTH([Date Column]@row)), ……….