Dashboard Metrics - Help with Formula

I am trying to set up a formula for a dashboard metric. I have a separate sheet for my metrics. The metrics reference a sheet called “Policies, Procedures, & Guidelines Inventory”.

There is one metric that requires referencing conditions in two columns. I was able to set up the formula with one condition, but I can’t figure out how to add another condition.

The goal of this particular dashboard metric is to count the number of “Approved/Published” policies in the “Policy Status” column that are less than 365 days in the “Number of Days Between Today and Next Review Date” column.  

This formula counts all policies less than 365 days:

=COUNTIF({Policies, Procedures, & Guidelines Invento Range 1}, <365)

Can anyone help me add the condition to count only “Approved/Published” policies from the “Policy Status” column?

I struggle with formulas. I just can’t seem to get them right.

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You would need to use the COUNTIFS function (with the S on the end) to be able to incorporate more than one range/criteria set.


    =COUNTIFS({Policies, Procedures, & Guidelines Invento Range 1}, <365, {Other Sheet Policy Status Column}, "Approved/Published")

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You would need to use the COUNTIFS function (with the S on the end) to be able to incorporate more than one range/criteria set.


    =COUNTIFS({Policies, Procedures, & Guidelines Invento Range 1}, <365, {Other Sheet Policy Status Column}, "Approved/Published")

  • alwayslearning
    alwayslearning ✭✭✭✭

    Paul -- I can't thank you enough. That worked perfectly. I struggle with formulas mostly because I don't understand them (even when reading all the countless information Smartsheet provides) but I also get lost with the commas, parentheses, etc. THANK YOU!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️


    Getting the hang of formulas can be a rather daunting task, but it will come with time and experience.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!