Cross sheet COUNTIFS

CyberJL · 05/22/25

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!

AdamSYNH · 05/23/25

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

Adam Murphy · 05/22/25

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 · 05/22/25

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

AdamSYNH · 05/23/25