Formula Error for Week over Week metrics by a secondary criteria

@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.

Please help :)

Best Answers

Answers

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

  • Leibel S
    Leibel S ✭✭✭✭✭✭

    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

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

  • Leibel S
    Leibel S ✭✭✭✭✭✭

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

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

    I need last week metrics and this week.

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @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!