Adding current month criteria to formulas

Travis Horton

Trying to add a specified month criterion to this formula from a date in the same cross-referenced 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.

Neil Watson

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

Travis Horton

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

Kelly Moore

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