Formula help with counting columns IF it has value
My bad formula that doesn't work
=countifs({link to a column in another sheet} = "monitoring", AND {link to a column in another sheet} = "in review") AND ({link to a column in another sheet}, "minor")
I essentially want it to count the number of "minor" risks are in "monitoring" OR "in review" from another spreadsheet. I tried to do the range vs entire column and it didn't work (plus MORE rows may be added so if I do a range vs entire column it won't count new ones). One column on that RAID log would be for "monitoring" and "in review" and another column in that same spreadsheet will be for "minor".
This is my bad formula where I counted range
=COUNTIFS({BenAdmin RAID Log Range 1} = "monitoring"), AND({BenAdmin RAID Log Range 1} = "in review"))+{BenAdmin overall impact}, "minor")
Answers
-
Hi @StaceyRxBenefits
Unfortunately, COUNTIFS really does not like OR statements. It is less clean looking solution, but given the information you have I would try something along the lines of:
=COUNTIFS({BenAdmin RAID Log Range 1}, "monitoring", {BenAdmin overall impact}, "minor") + COUNTIFS({BenAdmin RAID Log Range 1}, "in review", {BenAdmin overall impact}, "minor")
COUNTIFS already has built in AND statements between each range+criterion combo so you don't necessarily need to implement that into the statement. The above formula will take anything that is both "minor" and "monitoring" and add it to anything that is both "minor" and "in review."
Let me know if I can provide any more clarity or if I misunderstood the ask! -
Thank you for your response. When I add that formula above it shows INCORRECT ARGUEMENT SET I'm not sure what I'm doing wrong - may need to have a help call w/Smartsheet.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!