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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!