COUNTIFS keep coming back as zero

Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.

• Options

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!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!