COUNTIFS with other IF criteria and referencing other sheets

kolfinna

Hello!

Context: I'm leading the charge on tenant migration for a datacenter over 7 weeks. There are 10 teams (each with their own inventory sheet) and hundreds of services among all the sheets.

Goal: track completion metrics across all teams, one week at a time. This would include total number of services and number of successful migrations (aka Status=Complete). However, I have some rows marked to not be included on other reports, as they are parent rows and not child services. I use a double quote (") to filter these rows out on other reports.

Right now I'm attempting to summarize all that data into a report so I can feed that data into a dashboard with widgets, but I'm a little stuck on syntax. Pseudo-code is something like this:

If({sheet reference}[Comments] does not contain """,countifs({same sheet reference}[week]:[week]="1",[Status]:[Status]="Complete")))

TL;DR: people want to know how many services we're migrating each week, and how we're coming along in realtime.

May I please get your help? It's entirely possible I'm overthinking this. Is there a better way to track the metrics? I was able to get basic count criteria for all on a per-inventory basis, just wondering if I can put a couple other criteria in there to achieve greater granularity.

The only other thing I can think to do is pull sheet metrics on a report (e.g., create a report referencing all inventories matching week = 1 and comments does not contain ", then show the Status column), but then I'm not sure how I'd reference those in a dashboard chart/widget.

Thoughts?

Thanks in advance!

kolfinna

Answered my own question! :)

=COUNTIFS({Week},1,{Status}, "Complete", {Comments}, NOT(FIND("\"", LOWER(@cell)) > 0))

Basically what happened was this:

• All of my references are in the same source sheet
• I declared three references, one for each component because a single range with multiple columns isn't cooperative
• The escape character is what was killing me earlier. Turns out, if you put three quotes together without an escape character, things break. Imagine that. :)

This was a game changer: https://sourceforge.net/p/smartsheet/wiki/markdown_syntax/

kolfinna

Answered my own question! :)

=COUNTIFS({Week},1,{Status}, "Complete", {Comments}, NOT(FIND("\"", LOWER(@cell)) > 0))

Basically what happened was this:

• All of my references are in the same source sheet
• I declared three references, one for each component because a single range with multiple columns isn't cooperative
• The escape character is what was killing me earlier. Turns out, if you put three quotes together without an escape character, things break. Imagine that. :)

This was a game changer: https://sourceforge.net/p/smartsheet/wiki/markdown_syntax/