Formula Error for Week over Week metrics by a secondary criteria

Kristi Curry

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

Leibel Shuchat

The above shows last weeks numbers. For this week it is even more simple:

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

Mark Cronk

Hi @Kristi Curry ,

@Paul Newcome will have the real answer,, but I think the last criteria needs to be logical -> @CELL=

=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}, @CELL="Escalations Team")))

Work?

Mark

Leibel Shuchat

Yes.

But for the last month formula your if statement would need to check if the current month is 1 then we need month 12 otherwise use this month -1.

Kristi Curry

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

Kristi Curry

Leibel Shuchat

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

Kristi Curry

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

Leibel Shuchat

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

Kristi Curry

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

I need last week metrics and this week.

Leibel Shuchat

The above shows last weeks numbers. For this week it is even more simple:

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

Mark Cronk

Hi @Kristi Curry ,

@Paul Newcome will have the real answer,, but I think the last criteria needs to be logical -> @CELL=

=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}, @CELL="Escalations Team")))

Work?

Mark

Kristi Curry

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

Leibel Shuchat

Yes.

But for the last month formula your if statement would need to check if the current month is 1 then we need month 12 otherwise use this month -1.

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