Multi-Select Count Criteria

Amy Evans

Hello,

I want to count all Small projects where the Top 12 multi-select column has any value populated. The multi-select Top 12 column will have many blanks.

=COUNTIFS({All Projects}, "Small", {Top 12}, OR(....)

Thanks,

Amy

James Keuning

Can you use LEN?

=COUNTIF([Top 12]:[Top 12], LEN(@cell) > 0)

Jeff Reisman

Try this:

=COUNTIFS({All Projects}, "Small", {Top 12}, NOT(ISBLANK(@cell)))

Alternatively, if your Top 12 values will all be text values, or will all be number values, you can use ISTEXT or ISNUMBER in place of the not(isblank).

Amy Evans

Jeff - Perfect! Thank you very much. Worked like a charm.

Amy

Amy Evans

Jeff - Should I use "@cell" reference instead of calling out the specific project size name in quotes?

Jeff Reisman

https://community.smartsheet.com/discussion/comment/327230#Comment_327230

@cell is useful when you need to perform a function on cells in a range inside of another function. In the first half of your formula, you're simply evaluating the {All Projects} range to find the cells equal to "Small." In the second half, you need to use the NOT and ISBLANK functions on the cells in the {Top12} range before you can count them, so you use @cell to tell the COUNTIFS to first perform those functions on every cell in the range as it goes, to determine if it should be counted.

Here's an example using MONTH and DAY functions against date values in a range:

Amy Evans

Here's what I did in order to copy/paste a single formula down a dozen rows of different project size categories. Is this okay to do instead of writing out the "Small" etc..?

=COUNTIFS({All Projects}, @cell = [Project Size]@row, {Top 12}, NOT(ISBLANK(@cell)))

Jeff Reisman

That works, but you don't need the @cell for the Project Size. This will work just as well. In this case, the formula efficiency comes from using @row.

=COUNTIFS({All Projects}, [Project Size]@row, {Top 12}, NOT(ISBLANK(@cell)))

Here's a guide to using @cell and @row:

https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

Amy Evans

Thank you, Jeff!