COUNTIFS keep coming back as zero
Hello all,
I'm trying to do a count of all projects in Green (later for Yellow and Red) status across 4 different team sheets and this is the formula that I came up with :
=COUNTIFS({Team B Range 1}, FIND("Green", @cell) > 0, {Team F Range 1}, FIND("Green", @cell) > 0, {Team G Range 1}, FIND("Green", @cell) > 0, {Team V Range 1}, FIND("Green", @cell) > 0)
it seemed to work fine for Team B but when I added the other teams it now constantly shows 0 as my result. Can someone point out where I went wrong on this? This is on a summary sheet if that helps.
Thank you
Answers
-
Hi @Steven Deem,
I think the problem here is the criteria - you're trying to do the count across too many criteria.
If it's just a simple count then you would be better doing something along the lines of a COUNTIF/COUNTIFS for each range and add then together.
In addition are you using Status columns? If so, you don't need the FIND portion of your formula.
For example:
=COUNTIF({Team B Range 1},"Green" + COUNTIF({Team F Range 1},"Green")
This should show you the amount of green statuses in Team B & Team F - obviously you can add additional teams as required.
If the sheets are very similar you can also use a sheet summary & sheet summary report to grab the data and combine it easily.
Hope this helps somewhat, but if you've any questions etc. then just ask! 😊
-
The problem is that you are not able to reference two (or more) different sheets within the same function. You are going to need to break them apart into separate COUNTIFS and then add the individual COUNTIFS together.
-
Thank you for all the help! I broke them apart and added them up separately as suggested and it works just fine. You all are the best!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!