Count if contains and if not contains in the same cell
I need to count how many cells there are in a column IF that cell contains one thing AND does not contain another thing.
I can do one or another but having trouble combining it.
So far I have this and it works - =COUNTIFS({Master Project}, NOT(CONTAINS("HHA", @cell)))
It returns all cells which contain "HHA". Now I need to add in addition to containing "HHA" it should EXCLUDE those that contain "PCA"
I hope someone can help. I've been searching for answers in the Smartsheet community but haven't had any luck.
Answers
-
Hi @Tzippy ,
I think your formula would count the cells in the range that do not contain "HHA". To get a count of cells with "HHA" and excluding "PCA" this should work:
=COUNTIFS({Master Project}, CONTAINS("HHA", @cell), {Master Project}, NOT(CONTAINS("PCA", @cell)))
Hope this helps; if you've any questions then just ask! 😊
-
Thank you Nick, that worked.
Now I'm having an issue adding a third step. I want to add that it should count all cells that have a number 1 in another column on that row. (in addition to the other row having HHA and not PCA as the formula mentioned above)
Thank you
-
@Tzippy ,
You would just need to add another section to the COUNTIFS formula. If the column contains just numbers then it would be something along the lines of:
=COUNTIFS({Master Project}, CONTAINS("HHA", @cell), {Master Project}, NOT(CONTAINS("PCA", @cell)), {Number Column Ref},1)
Obviously you can have a different reference but you should be able to get the gist of this.
If the column with the 1/not 1 in it also has other text in it (as part of a text chain) then you can use CONTAINS similar to the other sections of the formula.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 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!