How many ranges/criteria can I use in a COUNTIFS formula?

Options

I need to look up multiple criteria under the same column with different spelling (like "In Process", INPROCESS, InProcess" and look up on the same sheet but different column a type (like store type).

The different Countifs I've tried either gives me a zero or unparseable, so I am stuck - Help!

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    Assuming your column with "In Process" is a column called "Status"...

    The OR function tells COUNTIFS to count cells in the Status column with any of the included values. CONTAINS searches for a text string and is not case-sensitive, so whether your status is In Process, inprocess, in Process, In PROGRESS, etc., the below would find and count them. Close out the OR statement, add a comma, and indicate the next range to search and next criteria to find.

    =COUNTIFS(Status:Status, OR(CONTAINS("process", @cell), CONTAINS("Progress", @cell)), StoreType:StoreType, "Clothing")

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    Assuming your column with "In Process" is a column called "Status"...

    The OR function tells COUNTIFS to count cells in the Status column with any of the included values. CONTAINS searches for a text string and is not case-sensitive, so whether your status is In Process, inprocess, in Process, In PROGRESS, etc., the below would find and count them. Close out the OR statement, add a comma, and indicate the next range to search and next criteria to find.

    =COUNTIFS(Status:Status, OR(CONTAINS("process", @cell), CONTAINS("Progress", @cell)), StoreType:StoreType, "Clothing")

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Mary G
    Options

    Thank you! I had tried that but it didn't work, so not sure what I did wrong but it is working now!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!