Need help Summary Formula Countif multiple criteria

Meg Y
Meg Y ✭✭✭✭✭✭

Have a formula - Issue is that the fit/gap can be one or the other not both

=(Countif([Client Final Approval]:[Client Final Approval], “Needs Review”))+ (Countif([Fit/Gap]:[Fit/Gap], “Gap – In Scope”)) + (Countif([Fit/Gap]:[Fit/Gap], “Fit – In Scope”))

________________________________________________________________________________________________________________________

If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Meg Young | Smartsheet Consultant mmyoungconsulting@gmail.com. Certified in Core Product, Project Management, and System Administration

Best Answer

  • Darren Mullen
    Darren Mullen Community Champion
    Answer ✓

    @Meg Y

    You're capturing rows that meet all of that criteria and adding them together.

    Maybe you are trying to combine the Client Final Approval with the Fit/Gap criterion?

    If so, use CountIFS()

    Countifs([Client Final Approval]:[Client Final Approval], “Needs Review”, [Fit/Gap]:[Fit/Gap], “Gap – In Scope”) + Countifs([Client Final Approval]:[Client Final Approval], “Needs Review”, [Fit/Gap]:[Fit/Gap], “Fit – In Scope”)

    ALTERNATIVELY

    Countifs([Client Final Approval]:[Client Final Approval], “Needs Review”, [Fit/Gap]:[Fit/Gap], or(@cell = “Gap – In Scope”, @cell = “Fit – In Scope”))

    Darren Mullen - Looking to take your Smartsheet knowledge to the next level and become an expert? Join the Smartsheet Guru Elite

    Get my 7 Smartsheet tips here

    Author of: Smartsheet Architecture Solutions

Answers

  • Darren Mullen
    Darren Mullen Community Champion
    Answer ✓

    @Meg Y

    You're capturing rows that meet all of that criteria and adding them together.

    Maybe you are trying to combine the Client Final Approval with the Fit/Gap criterion?

    If so, use CountIFS()

    Countifs([Client Final Approval]:[Client Final Approval], “Needs Review”, [Fit/Gap]:[Fit/Gap], “Gap – In Scope”) + Countifs([Client Final Approval]:[Client Final Approval], “Needs Review”, [Fit/Gap]:[Fit/Gap], “Fit – In Scope”)

    ALTERNATIVELY

    Countifs([Client Final Approval]:[Client Final Approval], “Needs Review”, [Fit/Gap]:[Fit/Gap], or(@cell = “Gap – In Scope”, @cell = “Fit – In Scope”))

    Darren Mullen - Looking to take your Smartsheet knowledge to the next level and become an expert? Join the Smartsheet Guru Elite

    Get my 7 Smartsheet tips here

    Author of: Smartsheet Architecture Solutions

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp Community Champion

    Hi @Meg Y

    You can use the OR(@cell=A,@cell=B) format.

    =COUNTIFS([Client Final Approval]:[Client Final Approval], "Needs Review", [Fit/Gap]:[Fit/Gap], OR(@cell = "Gap – In Scope", @cell = "Fit – In Scope"))

    Alternatively, you can use a helper column, as shown in the image below.

    =OR([Fit/Gap]@row = "Gap – In Scope", [Fit/Gap]@row = "Fit – In Scope")

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

    image.png
  • Meg Y
    Meg Y ✭✭✭✭✭✭
    edited 06/05/25

    Both columns are dropdowns. The formula is also for gap - out of scope and fit - out of scope which are also in the fit/gap column

    Client final approval dropdowns are Needs Review and Approved

    The numbers are four different widgets on the dashboard. Goal is use the sheet summary.

    ________________________________________________________________________________________________________________________

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

    Meg Young | Smartsheet Consultant mmyoungconsulting@gmail.com. Certified in Core Product, Project Management, and System Administration

  • Meg Y
    Meg Y ✭✭✭✭✭✭
    edited 06/05/25

    @Darren Mullen Thank you this formula works. Figured out that one of my criteria was typed incorrectly. Fixed that and viola

    =COUNTIFS([Client Final Approval]:[Client Final Approval], "Needs Review", [Fit/Gap]:[Fit/Gap], OR(@cell = "Gap – In Scope", @cell = "Fit – In Scope"))

    ________________________________________________________________________________________________________________________

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

    Meg Young | Smartsheet Consultant mmyoungconsulting@gmail.com. Certified in Core Product, Project Management, and System Administration

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!