Formula calculate last 30 days referenced sheets

Michelle Maas

=SUMIFS({Sales Pipeline Amount}, {Sales Pipeline Level}, "1", {Sales Pipeline Start}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))) + SUMIFS({Archive Sales Amount}, {Archive Sales Level 1}, "1", {Archive Sales Start}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))

I want to change the above formula to just show the last 30 days. I tried the below formula but the amount does not match the actual. There are two sheets the formula is referencing but the columns are the same and I only want to calculate the amount showing on level 1.

=SUMIFS({Sales Pipeline Amount}, {Sales Pipeline Level}, "1", {Sales Pipeline Start}, <TODAY(-30)) + SUMIFS({Archive Sales Amount}, {Archive Sales Level 1}, "1", {Archive Sales Start}, <TODAY(-30))

NicoLHC

@Michelle Maas i just wanted to visualize you how the calculation run based on two different formula. In order to simplify i split the total formula i two parts Pipeline and Archive .

Green is all data <today -30 —- archive = 50
Purple (Your last postet formula) is everything between today and the last 30 days - archive = 210

Green Formula

=SUMIFS({Sales Pipeline Amount}, {Sales Pipeline Start}, <=TODAY(-30), {Sales Pipeline Start}, <=TODAY()) + SUMIFS({Archive Sales Amount}, {Archive Sales Start}, <=TODAY(-30), {Archive Sales Start}, <=TODAY())

Purple Formula

=SUMIFS({Sales Pipeline Amount}, {Sales Pipeline Start}, >=TODAY(-30), {Sales Pipeline Start}, <=TODAY()) + SUMIFS({Archive Sales Amount}, {Archive Sales Start}, >=TODAY(-30), {Archive Sales Start}, <=TODAY())

NicoLHC

@Michelle Maas great approach maybe you can try this way.

=SUMIFS({Sales Pipeline Amount}, {Sales Pipeline Level}, "1", {Sales Pipeline Start}, >=TODAY() - 30, {Sales Pipeline Start}, <=TODAY())+SUMIFS({Archive Sales Amount}, {Archive Sales Level 1}, "1", {Archive Sales Start}, >=TODAY() - 30, {Archive Sales Start}, <=TODAY())

Michelle Maas

@NicoLHC Hi Nico thanks for getting back to me. It returns error: #INVALID OPERATION.

Paul Newcome

Think of dates as numbers on a number line. TODAY() is zero, anything in the past is a negative number, and anything in the future is a positive number.

You are correct with the TODAY(-30) piece if you want to evaluate based on 30 days in the past, but you want dates that are greater than, not less than, if you want to evaluate dates that move closer to today's date on the timeline.

Try swapping your less than symbols for greater than symbols and see what happens there.

Michelle Maas

@Paul Newcome thanks for your help. The formula should be less than < to show the past 30 days. What is weird is when I use this version for Prospects, it calculates the correct amount:

=SUM(COLLECT({Sales Pipeline Amount}, {Sales Pipeline Stage}, "1 - Prospect", {Sales Pipeline Start}, >=TODAY(-30), {Sales Pipeline Start}, <=TODAY()))

Edit: Ah, I think it figured it out… this >=TODAY(-30) gives the less than 30 days, and this <=TODAY())) stops the formula from adding anything past that amount of time. Therefore it answers the question. Thank you, Paul.

Michelle Maas

@Paul Newcome 

I'm using the below formula to calculate enquiries that have come in in the last 30 days. However, the total is not matching. I have checked the references are correct and tried to find the error in the referenced sheet, but can't work out why I am getting two different totals.. wondering if there is something in the formula I have missed.

=SUMIFS({Sales Pipeline Amount}, {Sales Pipeline Start}, >=TODAY(-30), {Sales Pipeline Start}, <=TODAY()) + SUMIFS({Archive Sales Amount}, {Archive Sales Start}, >=TODAY(-30), {Archive Sales Start}, <=TODAY())

NicoLHC

@Michelle Maas

i sliced the formula and we are just capturing the last value …. seems like

Purple Result:

=SUMIFS({Sales Pipeline Amount}, {Sales Pipeline Start}, >=TODAY(-30), {Sales Pipeline Start}, <=TODAY()) + SUMIFS({Archive Sales Amount}, {Archive Sales Start}, >=TODAY(-30), {Archive Sales Start}, <=TODAY())

Green Result :

=SUMIFS({Sales Pipeline Amount}, {Sales Pipeline Start}, <=TODAY(-30), {Sales Pipeline Start}, <=TODAY()) + SUMIFS({Archive Sales Amount}, {Archive Sales Start}, <=TODAY(-30), {Archive Sales Start}, <=TODAY())

Michelle Maas

@NicoLHC thanks for trying to help. I'm not following what you are saying. If I run a report and set the dates 19 Aug to 19 Sep, add the Amount column and compare it to the formula cell, I am out by +£30K. I've checked the report fields, dates, anything I can think of but it seems its the formula as the data is correct. Does the formula include the 19th or is it up to the day before?

NicoLHC

@Michelle Maas i just wanted to visualize you how the calculation run based on two different formula. In order to simplify i split the total formula i two parts Pipeline and Archive .

Green is all data <today -30 —- archive = 50
Purple (Your last postet formula) is everything between today and the last 30 days - archive = 210

Green Formula

=SUMIFS({Sales Pipeline Amount}, {Sales Pipeline Start}, <=TODAY(-30), {Sales Pipeline Start}, <=TODAY()) + SUMIFS({Archive Sales Amount}, {Archive Sales Start}, <=TODAY(-30), {Archive Sales Start}, <=TODAY())

Purple Formula

=SUMIFS({Sales Pipeline Amount}, {Sales Pipeline Start}, >=TODAY(-30), {Sales Pipeline Start}, <=TODAY()) + SUMIFS({Archive Sales Amount}, {Archive Sales Start}, >=TODAY(-30), {Archive Sales Start}, <=TODAY())

Michelle Maas

@NicoLHC thanks for the explanation. This morning I did a recalculation of the data sheet to compare totals and somehow now they are matching, although they weren't yesterday, which is odd - I did not change the formula. All sorted now and I appreciate your and @Paul Newcome help to get it right. Have a lovely weekend.

Paul Newcome

Happy to help. 👍️

Note: Because your formula uses "less than or equal to" and "greater than or equal to", it should have been including the 19th of Sep. when you asked on the 19th. But something to keep in mind… Your formula is only evaluating for the past thirty days and not necessarily the past month. August has 31 days, so you were pulling from 20 Aug to 19 Sep.. You were not getting anything from the 19th of Aug. because that was technically more than 30 days in the past. That may be why your numbers seemed off.