Help w/COUNTIFS and multiple NOT(CONTAINS) functions
Hi team - I created a formula to count the number of numbers of errors in a specific month and does NOT CONTAIN "general counsel" in the Division Requested By column, the June example works:
June example formula:
=COUNTIFS([Main row]:[Main row], 1, Created:Created, IFERROR(MONTH(@cell), 0) = 6, [Incorrect format]:[Incorrect format], 1, [Division requested by]:[Division requested by], NOT(CONTAINS("general counsel", @cell)))
I now need to add another NOT(CONTAINS) formula to not count "Low" items in the IRR column but I can't seem to get the formula right. I'm using:
=COUNTIFS([Main row]:[Main row], 1, Created:Created, IFERROR(MONTH(@cell), 0) = 7, [Incorrect format]:[Incorrect format], 1, [Division requested by]:[Division requested by], NOT(CONTAINS("general counsel", @cell), IRR:IRR, NOT(CONTAINS("low", @cell))))
What am I missing?
Best Answer
-
It is just a misplaced parenthesis. You closed out your first CONTAINS, but didn't close out the NOT associated with it.
=COUNTIFS([Main row]:[Main row], 1, Created:Created, IFERROR(MONTH(@cell), 0) = 7, [Incorrect format]:[Incorrect format], 1, [Division requested by]:[Division requested by], NOT(CONTAINS("general counsel", @cell)), IRR:IRR, NOT(CONTAINS("low", @cell)))
Adding in that closing parenthesis (in bold) and removing one from the end should clear up the issue for you.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
Hi @lewis hamilton - unfortunately I have too many criteria in the IRR column and it's "easier" to figure out what NOT to count vs including every possible option of the items that need to be counted. In my case, I have a 2 NOT(CONTAINS) to use for 2 different columns.
-
It is just a misplaced parenthesis. You closed out your first CONTAINS, but didn't close out the NOT associated with it.
=COUNTIFS([Main row]:[Main row], 1, Created:Created, IFERROR(MONTH(@cell), 0) = 7, [Incorrect format]:[Incorrect format], 1, [Division requested by]:[Division requested by], NOT(CONTAINS("general counsel", @cell)), IRR:IRR, NOT(CONTAINS("low", @cell)))
Adding in that closing parenthesis (in bold) and removing one from the end should clear up the issue for you.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@lewis hamilton Smartsheet does not have a SUMPRODUCT function built in.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Paul Newcome - doh, the dreaded missing parenthesis!!!!! That did the trick - thanks so much!!!
-
Happy to help. 👍️
And yes. Those parenthesis can be a pain. I also have problems with forgetting square brackets sometimes too. Those aren't too bad in Smartsheet because you don't get all of the colors and end up with an error, but here in the Community I tend to forget them sometimes. Haha.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 378 Global Discussions
- 208 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!