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
-
@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! 😊
-
No problem, happy to have helped! 😊
Answers
-
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! 😊
-
@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
-
@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! 😊
-
This worked perfectly! Thank you @Nick Korna!!
Aleya Krenek
Education Service Center Region 13
Grants and Contracts Project Coordinator
-
No problem, happy to have helped! 😊
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!