Using Count ifs to Check 3 columns and validations for a dashboard.

Devg
Devg
edited 01/17/25 in Formulas and Functions

Using Countifs to Check Origin of error with the reference to the month and validation already provided in the data. Everytime I complete the first criteria the option to select the column from reference sheet gets disabled. Please suggest a workaround.

Answers

  • Here are the columns:

  • Devg
    Devg
    edited 01/17/25

    I entered this, =COUNTIFS({UMMS Error Resolution Tracker Range 5},[Column3]51), ({UMMS Error Resolution Tracker Range 6}, [Column2]@row), ({UMMS Error Resolution Tracker Range 7, "True"})) Got Unparseble and then ran AI , got this formula:

    =COUNTIFS({UMMS Error Resolution Tracker Range 5}, [Column3]51, {UMMS Error Resolution Tracker Range 6}, [Column2]@row, {UMMS Error Resolution Tracker Range 7}, "True")

    Which is giving a new error, #Invalid Ref, in capitals, So Rude!

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp Community Champion

    Hi @Devg

    In the demo solution sheet below, I used the following to use the COUNTIFS function.

    [Dec] =COUNTIFS({UMMS Error Resolution Tracker : How}, [How was the error found?]@row, {UMMS Error Resolution Tracker : Date}, MONTH(@cell) = 12, {UMMS Error Resolution Tracker : Verified}, true)
    [Jan] =COUNTIFS({UMMS Error Resolution Tracker : How}, [How was the error found?]@row, {UMMS Error Resolution Tracker : Date}, MONTH(@cell) = 1, {UMMS Error Resolution Tracker : Verified}, true)

    Explanation of the Formula:

    The formula calculates the number of rows in a reference Smartsheet that meet three specific conditions:

    1. How was the error found? matches the current row's value in the column [How was the error found?]@row.
    2. The error occurred in the specified month (December or January) based on the Date column.
    3. Verified is marked as true.

    Here’s a breakdown of each formula:

    Formula for December:

    [Dec] =COUNTIFS({UMMS Error Resolution Tracker : How}, [How was the error found?]@row, {UMMS Error Resolution Tracker : Date}, MONTH(@cell) = 12, {UMMS Error Resolution Tracker : Verified}, true)

    • {UMMS Error Resolution Tracker : How}: References the "How was the error found?" column in the source sheet.
    • [How was the error found?]@row: Refers to the current row's value for comparison.
    • {UMMS Error Resolution Tracker : Date}: References the date column in the source sheet.
    • MONTH(@cell) = 12: Ensures only rows where the date is in December are counted.
    • {UMMS Error Resolution Tracker : Verified}: References the "Verified" column in the source sheet.
    • true: Counts rows where the "Verified" checkbox is checked.

    In the above formula, @cell in the MONTH(@cell) condition put the data values of the range {UMMS Error Resolution Tracker : Date} one by one for each row.

    In the last condition, true is a special logic value. So, do not put "" around it like "true". Alternatively, you can put 1 to stand for true.

    For January, you change the MONTH(@cell) = 12 to MONTH(@cell) = 1.

    https://app.smartsheet.com/b/publish?EQBCT=ef5322cb3f50444383bab8c89707e7d3

    This is a sample data sheet based on your description. Note the cause -"Audit - …" and Verified Error checked counts 5, which matches the calculated result above.

    https://app.smartsheet.com/b/publish?EQBCT=8797de198a6341c78c7657333e050e14 (Source Sheet)

  • Thanks a Ton!, I was going wrong with entering another bracket at the end of the 2nd query which allowed me to select additional ranges.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!