Formula to pull metrics for previous month

Ashley Admire

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")

Austin Urban JCI

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")

Ashley Admire

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?

Paul Newcome

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))

Ashley Admire

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?

Paul Newcome

How exactly are you populating the month and year columns in the source sheet?

Ashley Admire

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?

Ops PI Dashboard.JPG

Monthly Widgets on Dashboard.JPG

Copy Workflow.JPG

Station Trends.JPG

Ops PI Data Sheet.JPG

Paul Newcome

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.

Ashley Admire

what would that helper column look like?

Paul Newcome

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.