Combination of CountIFS and OR

I am trying to accomplish this:

Count If:

If {Current Development - Initiative}, ="Concur HS4"

And, If {Current Development - Hierarchy}, ="EPIC"

And If either one of these columns is not Empty

{Current Development - Q2'23} <> "", {Current Development - Q3'23} <> ""

This is the formula I used, but it shows #Invalid Operation

=COUNTIFS({Current Development - Initiative}, ="Concur HS4", {Current Development - Hierarchy}, ="EPIC", OR({Current Development - Q2'23} <> "", {Current Development - Q3'23} <> ""))

Any advice is much appreciated :-)

SYLVIA KAY | DIRECTOR PROJECT MANAGEMENT

PLATFORM ENGINEERING & TECHNOLOGY TEAM

AMERICAN EXPRESS GLOBAL BUSINESS TRAVEL

M: 404-664- 1025 | SYLVIA.KAY@AMEXGBT.COM

AMEXGLOBALBUSINESSTRAVEL.COM

follow us on twitter: @AmexGBT

follow us on instagram: @AmexGBT

Tags:

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    @Sylvia Kay The #INVALID OPERATION error points to an operator issue in your formula. In this case, you're missing commas before the "not equal" signs in your OR:

    Also, the equal signs in front of your text criteria are not needed. This is because the = is implied by listing the criteria for the range in the criteria range, criteria format required by COUNTIFS.

    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!

  • Sylvia Kay
    Sylvia Kay ✭✭✭✭✭

    Hi Jeff, thank you for looking at this!

    I made the changes as suggested, but now I get another error: "INVALID DATA TYPE"

    This is the current formula:

    =COUNTIFS({Current Development - Initiative}, "Concur HS4", {Current Development - Hierarchy}, "EPIC", OR({Current Development - Q2'23}, <>"", {Current Development - Q3'23}, <>""))

    SYLVIA KAY | DIRECTOR PROJECT MANAGEMENT

    PLATFORM ENGINEERING & TECHNOLOGY TEAM

    AMERICAN EXPRESS GLOBAL BUSINESS TRAVEL

    M: 404-664- 1025 | SYLVIA.KAY@AMEXGBT.COM

    AMEXGLOBALBUSINESSTRAVEL.COM

    follow us on twitter: @AmexGBT

    follow us on instagram: @AmexGBT

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    The cause of that error is: The formula contains or references an incompatible data type, such as =INT("Hello")

    In this case, the way we're using OR won't work with COUNTIFS, and that's causing the error.

    This is one of those cases where using a helper column will make it work much easier. Try this: Create a helper text/number column in your source sheet called something like "DevelopmentCount" and use this formula:

    =IF([Current Development - Q2'23]@row <>"", 1, 0) + IF([Current Development - Q3'23]@row <>"", 1, 0)

    Make it a column formula so it applies to all rows. The logic here is that if neither cell has a value in it, the cell will equal 0; if one is not blank, the cell will equal 1, and if both are not blank, the cell will equal 2.

    Now on your other sheet:

    =COUNTIFS({Current Development - Initiative}, "Concur HS4", {Current Development - Hierarchy}, "EPIC", {Development Count Column Range}, >= 1)

    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!

  • Sylvia Kay
    Sylvia Kay ✭✭✭✭✭

    Hi Jeff, thank you so much, this works! Much appreciated!

    SYLVIA KAY | DIRECTOR PROJECT MANAGEMENT

    PLATFORM ENGINEERING & TECHNOLOGY TEAM

    AMERICAN EXPRESS GLOBAL BUSINESS TRAVEL

    M: 404-664- 1025 | SYLVIA.KAY@AMEXGBT.COM

    AMEXGLOBALBUSINESSTRAVEL.COM

    follow us on twitter: @AmexGBT

    follow us on instagram: @AmexGBT

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!