# countifs referencing another sheet

Options
✭✭✭

Hi.

I want to insert a formula in one sheet that references a column in another sheet where is counts if the symbol is Yellow, green or red. I can get it to count for yellow but unsure how to add green and red.

Thanks,

• ✭✭✭✭✭✭
Options

@Edit Kalb In that case you have two options. You can count all three together or you can count everything that is not blue.

All three together:

=COUNTIFS({Sheet Reference}, OR(@cell = "Red", @cell = "Yellow", @cell = "Green"))

Everything except for blue:

=COUNTIFS({Sheet Reference}, @cell <> "Blue")

• ✭✭✭✭
Options

Hi,

I think you're looking for a COUNTIFS formula such as: =COUNTIFS({Sheet Reference}, "Yellow", {Sheet Reference}, "Green", {Sheet Reference}, "Red"). Hopefully that helps!

Cheers,

• ✭✭✭
Options

Thank you. Here is my formula:

=COUNTIFS({noww}, "Yellow", {noww}, "Green", {noww}, "Red")

It returns a value of zero which isn't correct. (This is an improvement. I was getting imparseable before. :) )

Thanks!

• ✭✭✭✭✭✭
Options

Tat is because COUNTIFS has "AND" built in to the back end. Your formula is essentially saying to count where a cell is equal to each of the three colors all at the same time which is not possible.

Are you trying to count all three of those colors and exclude certain data, or are you wanting individual counts for each color?

• ✭✭✭
Options

Ahhh, ok.

So I want to count all three together. I have a blue color also that I want to be excluded from the count.

• ✭✭✭✭✭✭
Options

@Edit Kalb In that case you have two options. You can count all three together or you can count everything that is not blue.

All three together:

=COUNTIFS({Sheet Reference}, OR(@cell = "Red", @cell = "Yellow", @cell = "Green"))

Everything except for blue:

=COUNTIFS({Sheet Reference}, @cell <> "Blue")

• ✭✭✭
Options

Thank you! That worked!❤️

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!