COUNTIF with array constant

Ana Guilherme · 04/18/23

Hello,

Can something like this be done in Smartsheet?

Thank you

Paul Newcome · 04/19/23

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))

Paul Newcome · 04/19/23

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)

Ana Guilherme · 04/19/23

Hello Paul,

@Paul Newcome, thank you for your answer.

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!

Paul Newcome · 04/19/23

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?

Ana Guilherme · 04/19/23

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.

Paul Newcome · 04/19/23

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))

Ana Guilherme · 04/21/23

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

Much appreciated!

Wish you a good day and happy weekend!😉

Paul Newcome · 04/21/23

Happy to help. 👍️

COUNTIF with array constant

Best Answer

Answers

Help Article Resources

Categories