Countifs
Hello
I am having trouble with this formula.
I am trying to count the total number of entries per week. Here's the formula
=COUNTIFS({NESV Tracker Range 1}, "5GC" + "EPC", {NESV Tracker Range 2}, >=[Column2]@row, {NESV Tracker Range 2}, <=[Column3]@row)
I get 0's when there should be different results.
If I do "5GC" alone it works:
Or if I do "EPC" it also works:
I need a formula that added both
Thanks
Answers
-
Hey @Cristian
The zero is caused by the way you are describing the EG and 5GC. As you have it written, smartsheet thinks you are concatenating the two to become 5GCEG. The Formula correctly returns a zero for that term.
In your data set, are the choices a single select cell, or multiselect (it could have both EG and 5GC in the same cell)? The formula below assumes a single-select cell based on your working formulas above.
=COUNTIFS({NESV Tracker Range 1}, OR(@cell="5GC", @cell="EPC"), {NESV Tracker Range 2}, >=[Column2]@row, {NESV Tracker Range 2}, <=[Column3]@row)
Will this work for you?
Kelly
-
Hi @Kelly Moore
The formula works. Thanks.
And thanks for making me understand the concatenating portion of formula. Also, thanks for clarifying the @cell portion. Did not know that. Thanks again.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!