Formula to pull metrics for previous month
I currently have a formula that pulls metrics into a sheet for a dashboard widget, it currently pulls the data if the month in the month column is the current month. I need it to pull the metrics for the previous month but I can't get it to work. What am I missing? I tried adding a -1 behind the MONTH(TODAY())-1 but that didn't work.
=IFERROR(SUMIFS({NE Peaking OPG.1 Monthly}, {NE Peaking Month} = MONTH(TODAY()), {NE Peaking Year} = YEAR(TODAY())), "0")
Answers
-
What error is it giving you if you remove the "IFERROR" catch? I tested a couple configurations and doing:
=MONTH(TODAY()) resulted in the value "3"
and
=MONTH(TODAY())-1 resulted in the value "2"
I think could be the issue is the SUMIFS syntax... I think you need to adjust it to match the suggested:
So splitting the formula from:
=IFERROR(SUMIFS({NE Peaking OPG.1 Monthly}, {NE Peaking Month} = MONTH(TODAY()), {NE Peaking Year} = YEAR(TODAY())), "0")
to:
=IFERROR(SUMIFS({NE Peaking OPG.1 Monthly}, {NE Peaking Month}, =MONTH(TODAY()), {NE Peaking Year}, =YEAR(TODAY())), "0")
-
I removed the IF ERROR so now my formula looks like this.
=(SUMIFS({MD Peaking OPG.1 Monthly}, {MD Peaking Month} = MONTH(TODAY()) - 1, {MD Peaking Year} = YEAR(TODAY())))
but now I get invalid operation error.
The formula you suggested didn't look different than what I had other than 2 additional commas, is that correct?
-
Try this:
=SUMIFS({NE Peaking OPG.1 Monthly}, {NE Peaking Month}, @cell = MONTH(TODAY()) + IF(MONTH(TODAY()) = 1, 11, -1), {NE Peaking Year}, @cell = YEAR(TODAY()) - IF(MONTH(TODAY()) = 1, 1, 0))
-
I'm not getting an error with that formula however it's returning a zero every time which isn't correct. Just to confirm - this formula should pull metrics for the previous month current year?
-
How exactly are you populating the month and year columns in the source sheet?
-
Every month each plant enters their metrics for the previous month using a web form which populates "Ops PI Data Sheet".
Each plant has a "Station Trends" sheet that is populated using an automated workflow on the "Ops PI Data Sheet" that tells it to copy the row to the correct plant's "Station Trends" sheet.
So the formula in question references the plant "Station Trends" sheet.
The final product we are trying to reach is a dashboard that has widgets for each plant, summarizing the previous month's data. I can share the workspace with you if that would help?
-
There's the problem. You are using text values instead of numbers for the months. You can't subtract a number from a text value. My suggestion would be to either switch the Month column to be numbers or use a helper column with a formula to output a number based on the Month selection.
-
what would that helper column look like?
-
It would be a text/number column with a nested IF statement to output the month number based on the text in the month selection column.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!