COUNT/NOT/OR function?

Hi! I'm looking to add a function in my sheet summary titled "Other". I want the formula to count the number of grants that have a status of "Green" and do NOT have the following funding source "TEA - Federal", "TEA - State", or "Local". We will add more funding sources in the future, so using NOT would be ideal. I have tried the COUNTIF(NOT()) function, COUNTIFS(NOT()), and IF(COUNT(NOT()))--this was not a good formula to use since I cannot fulfill the "then" statement of the IF function. I've tried incorporating @cell, but I'm not sure if I am using it correctly. The status column is titled "Status" and is a symbol column w/the RYGG harvey balls. I don't know where to begin with the formula--any ideas??


Aleya Krenek

Education Service Center Region 13

Grants and Contracts Project Coordinator

Best Answers

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    Hi @akrenek,

    This should do what you're after:

    =COUNTIFS(Status:Status, "Green", [Funding Source]:[Funding Source], (NOT(OR(@cell = "Local", @cell = "TEA - State", @cell = "TEA - Federal"))))

    This is based on the assumption that you won't have green status without a funding source. If you then wanted to add more exclusions then hopefully you can see the pattern of what to add into the NOT/OR portion of the formula.

    Hope this helps, any questions etc. then just ask! 😊

  • akrenek
    akrenek ✭✭✭✭

    @Nick Korna--Thank you so much! This did work! I found one caveat--it adds the funding sources that are blank and green. How do I exclude the blank cells too? I tried adding @cell=ISBLANK() to the NOT(OR()) portion of the formula and received #UNPARSEABLE. Do you have another idea?

    Aleya Krenek

    Education Service Center Region 13

    Grants and Contracts Project Coordinator

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    @akrenek just add a small extra bit to the NOT bit of the formula:

    =COUNTIFS(Status:Status, "Green", [Funding Source]:[Funding Source], (NOT(OR(@cell = "Local", @cell = "TEA - State", @cell = "TEA - Federal", @cell = ""))))

    This will now not include blank funding rows along with the already excluded ones.

    Hope this helps! 😊

  • akrenek
    akrenek ✭✭✭✭

    This worked perfectly! Thank you @Nick Korna!!

    Aleya Krenek

    Education Service Center Region 13

    Grants and Contracts Project Coordinator

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    No problem, happy to have helped! 😊

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!