# Formula Error for Week over Week metrics by a secondary criteria

Options
✭✭✭

@Paul Newcome I had a pro desk appointment today but we were unable to solve why the formula will not work. They recommended I reach out here for assistance. I have been trying to figure this out now for a week and have searched every post in Community and had 2 Pro Desk appointments trying to solve the issue. I have gotten Unparsable and Incorrect Argument errors. I'm at a loss on what else to try. I need to filter the weekly metrics by a second criteria.

Here was what was provided to me:

=IF(WEEKNUMBER(TODAY()) = 1, COUNTIFS({Created Date Range}, IFERROR(WEEKNUMBER(@cell), 0) = 52, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - 1), COUNTIFS({Created Date Range}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()) - 1, {Created Date Range}, IFERROR(YEAR(@cell), 0 = YEAR(TODAY()), {Item Owner}, "Escalations Team")))

It works fine until you add the last part for "Item Owner" needs to be Escalations Team.

I need to be able to pull metrics from a sheet for This Weeks Open Items that are assigned to a Team (drop down value in the sheet). There are 3 teams and I need to have This Week versus Last Week views for each team. Also looking to do This Week versus Last Week for a Escalation Reason type or a Individual Employee assigned. These are key metrics and KPIs I need in our dashboard.

• ✭✭✭
Options

This was the original formula we were building off of and that worked until the 2nd data point was added.

=IF(WEEKNUMBER(TODAY()) = 1, COUNTIFS({Created Date}, IFERROR(WEEKNUMBER(@cell), 0) = 52, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - 1), COUNTIFS({Created Date}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()) - 1, {Created Date}, IFERROR(YEAR(@cell), 0 = YEAR(TODAY()))))

• ✭✭✭
Options
• ✭✭✭✭✭✭
Options

Kristi,

Looks like you had a Typo in the above.

Try the below:

=IF(WEEKNUMBER(TODAY()) = 1, COUNTIFS({Created Date}, IFERROR(WEEKNUMBER(@cell), 0) = 52, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - 1), COUNTIFS({Created Date}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()) - 1, {Created Date}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), {Item Owner}, "Escalations Team"))

There was a parentheses missing from the end of IFERROR(YEAR(@cell), 0

• ✭✭✭
Options

@Leibel S This gives me a INVALID REF Error. :(

• ✭✭✭✭✭✭
Options

That is probably the name of the range... Rename the {Created Date} to however you have it named...

• ✭✭✭
Options

I just got it to work. Now how do I change it for last week?

I need last week metrics and this week.

• ✭✭✭
Options

If I switch this to month can I just change the week number reference to MONTH?

• ✭✭✭✭✭✭
Options

@Kristi Curry Give these a try...

Current Month:

=COUNTIFS({Created Date}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())), {Item Owner}, "Escalations Team")

Previous Month:

=COUNTIFS({Created Date}, AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), IFERROR(YEAR(@cell), 0) = IF(MONTH(TODAY()) = 1, YEAR(TODAY()) - 1, YEAR(TODAY()))), {Item Owner}, "Escalations Team")

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!