Adding current month criteria to formulas
Trying to add a specified month criterion to this formula from a date in the same crossreferenced sheet as this formula:
=AVERAGEIF({Avg Order Submission  KPI Range 1}, {Avg Order Submission  KPI Range 4}, {Avg Order Submission  KPI Range 2})
I've tried every variation of the MONTH function I can think of. Here's my last attempt.
=IF(MONTH({Avg Order Submission  KPI Range 6} = 12, AVERAGEIF{Avg Order Submission  KPI Range 1}, {Avg Order Submission  KPI Range 3}, {Avg Order Submission  KPI Range 2}))
My goal is to be able to see average by month.
Answers

@Travis Horton checking on the AVERAGEIF formula and looking at your formula and the bold section, the criteria normally refers to single cell or value...is this the case?
=AVERAGEIF({Avg Order Submission  KPI Range 1}, {Avg Order Submission  KPI Range 4}, {Avg Order Submission  KPI Range 2})

Yes, Avg Order Submission  KPI Range 4 references a single cell.

Hey @Travis Horton
There may be other ways to accomplish your goal. For example, a report, grouped my month, might give you exactly what you are looking for without formulas.
The correct syntax for the formula you have written above is below. I added an ISDATE in case you have non date cells in your range.
IF(COUNTIFS({Avg Order Submission  KPI Range 6},ISDATE(@cell), {Avg Order Submission  KPI Range 6}, MONTH(@cell)= 12)>0, AVERAGEIF({Avg Order Submission  KPI Range 1}, {Avg Order Submission  KPI Range 3}, {Avg Order Submission  KPI Range 2}))
If you wanted to average multiple criteria at once, you would use an AVG/COLLECT combination.
To build on your formulas in the future, a good practice is to change the generically named Range number that smartsheet assigns to the actual column name the range references. You can do this before clicking the Insert Reference into your destination sheet.
cheers,
Kelly
Help Article Resources
Categories
Check out the Formula Handbook template!