COUNTIFS keep coming back as zero

Steven Deem

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

Nick Korna

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! 😊

Paul Newcome

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.

Steven Deem

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!

Paul Newcome

Happy to help. 👍️