# COUNTIF with array constant

Hello,

Can something like this be done in Smartsheet?

Thank you

Tags:

• ✭✭✭✭✭✭

In that case I am going to suggest a slightly different structure to allow for more flexibility. Instead of having your array across the top row, I would suggest having your array listed down a single column. Then you would use this:

=COUNTIFS(Item:Item, HAS(Array:Array, @cell))

• ✭✭✭✭✭✭

What exactly are you trying to do? Get the count of cells in [Column A] that contain "Apples"?

=COUNTIFS(A:A, @cell = "Apples")

or

=COUNTIFS(A:A, @cell = F1)

• Hello Paul,

I'm trying to have a countif were I have a "centralized" array and if that array changes the countif is still valid and I do not need to change the several formulas that use each word in the array.

Basically I have a lot of formulas that use the terms "apples", "bananas" and "lemons" (for this example) and in case I need to change from "apples" to "pear" I only do that on one cell.

I understand that if I use =COUNTIFS(A:A, @cell = F1) instead of writting the name of the fruit in the formula already gives me the flexibility of if I change F1 to something else the formula is still valid, but the second aspect (described below would not apply).

The other thing about the above is... The countif (I'm using Or(contains)) is to long to write and also that's why I was seeking for a way (if exists) to only select an array instead a selection of each item.

The second aspect is if I need to increase the array of search from 3 items (apples, bananas and lemons) to 4 items (apples, bananas, mango and lemons) the countif would automatically adjust.

Thank you!

• ✭✭✭✭✭✭

So you are not looking for a count of JUST apples. You want a count of everything that is in the list across the top row?

Is [Column A] multi-select, or would there only ever be a single entry in each cell?

• So you are not looking for a count of JUST apples. You want a count of everything that is in the list across the top row? YES! thank you!

Always and only single-entry in each cell

Thank you.

• ✭✭✭✭✭✭

In that case I am going to suggest a slightly different structure to allow for more flexibility. Instead of having your array across the top row, I would suggest having your array listed down a single column. Then you would use this:

=COUNTIFS(Item:Item, HAS(Array:Array, @cell))

• Thank you!! Work perfectly!! Thank you so much!!!

Much appreciated!

Wish you a good day and happy weekend!😉

• ✭✭✭✭✭✭

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!