COUNT/NOT/OR function?

akrenek

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??

Nick Korna

@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! 😊

Nick Korna

No problem, happy to have helped! 😊

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

@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?

Nick Korna

@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

This worked perfectly! Thank you @Nick Korna!!

Nick Korna

No problem, happy to have helped! 😊