Cross sheet COUNTIFS

Smartsheet Braintrust - I need your help. I am creating a sheet with a few metrics, and one of them is Incidents Closed in the Last 7 Days. The metrics sheet and incidents sheet are separate, so there need to be cross-sheet references.

The incidents sheet includes many columns, but the ones relevant to what I need are Incident ID, Date Closed, and Incident Level.

Can you please assist with a formula that will count the number of incidents (by Incident ID) where the Date Closed is in the last 7 days AND Incident Level one of Level 1, Level 2, Level 3, Level 4, or Level 5?

THANK YOU!

Find more posts tagged with

Accepted answers

AdamSYNH

Hi @CyberJL,

Are there further incident levels beyond level 5? If not then you could add the following:

=COUNTIFS({Date Closed cross sheet reference}, > TODAY(-7), {Incident Level cross sheet reference}, <>"")

If you do have additional incident levels then you can just add them up for levels 1-5:

=COUNTIFS({Date Closed cross sheet reference}, > TODAY(-7), {Incident Level cross sheet reference}, "Level 1") +

COUNTIFS({Date Closed cross sheet reference}, > TODAY(-7), {Incident Level cross sheet reference}, "Level 2") +

COUNTIFS({Date Closed cross sheet reference}, > TODAY(-7), {Incident Level cross sheet reference}, "Level 3") +

COUNTIFS({Date Closed cross sheet reference}, > TODAY(-7), {Incident Level cross sheet reference}, "Level 4") +

COUNTIFS({Date Closed cross sheet reference}, > TODAY(-7), {Incident Level cross sheet reference}, "Level 5")

All comments

Adam Murphy

Hi @CyberJL , try this: =COUNTIFS({Date Closed}, > TODAY() - 7, {Incident Level}, OR(CONTAINS("Level 1", @cell), CONTAINS("Level 2", @cell), CONTAINS("Level 3", @cell), CONTAINS("Level 4", @cell), CONTAINS("Level 5", @cell))) I didn't try, but it should work (you have to create those cross sheet references for Date Closed and Incident Level). Hope this helps.

CyberJL

Thank you for the quick response! Unfortunately, it returns #INCORRECT ARGUMENT even after I create the cross sheet references.