COUNTIF with HAS Formula in Sheet Summary

Julie Tausch

I am trying to do a COUNTIF in the Sheet Summary for a column that is a Multi Select Dropdown. I know that you are supposed to use the HAS function, but I can only seem to get that to work in the sheet with @Cell. Any suggestions?

=COUNTIF([Department of Concern]:[Department of Concern], HAS([Department of Concern]:[Department of Concern], "PCC"))

The formula gives me an answer of 0 but there are several cells that have PCC selected.

Genevieve P.

Hi @Julie Tausch

Are you receiving a specific error when you use @cell? If so, is it possible that the syntax isn't quite right?

@cell will need to be typed fully in lower-case in order for it to be recognized, but it should work in a Sheet Summary Field:

Patrick Richter

Try =COUNTIF([Department of Concern]:[Department of Concern], "PCC") without the has.

Julie Tausch

I tried that, but it only counts the cells that ONLY have "PCC" selected. If there is more than one choice selected, it doesn't show in the total. I should have 12 but I get 9 using that formula.

Genevieve P.

Hi @Julie Tausch

You're right, you will need to use @cell in your HAS function to indicate that it should look into each of the cells in the previously stated range.

Try this:

=COUNTIF([Department of Concern]:[Department of Concern], HAS(@cell, "PCC"))

Cheers!

Genevieve

Julie Tausch

The problem is that @cell doesn't work in the Sheet Summary tab.

Genevieve P.

Hi @Julie Tausch

Are you receiving a specific error when you use @cell? If so, is it possible that the syntax isn't quite right?

@cell will need to be typed fully in lower-case in order for it to be recognized, but it should work in a Sheet Summary Field:

Julie Tausch

I'm not sure what was wrong the first time but it works now so I must've had something wrong in the syntax. THANK YOU!

Genevieve P.

No problem! 🙂 I'm glad you got it working.

Prime George

This was very helpful! Thank you @Genevieve P. !

jdpruett

I am having the same issue. The count for Central should be 2 and Northeast 1, but neither values in the box with two selected are being counted. Here's the formula:

=COUNTIF([Land Plan Area]:[Land Plan Area], HAS(@cell, "Central"))

Genevieve P.

Hey @jdpruett

Since "Land Plan Area" is just a text column, try using the CONTAINS function instead:

=COUNTIF([Land Plan Area]:[Land Plan Area], CONTAINS("Central", @cell))

Let us know if that worked!

Cheers,

Genevieve

NickBlocker

Hi SmartSheet Gurus!

I am having a similar however; slightly different issues. I am trying to count multiple text criteria in the same column. I can get the formula to work if I only use one criteria however; haven't been able to get it to work by adding additional criteria (I get 0 value).

Thanks in advance for your assistance :)

Genevieve P.

Hi @NickBlocker

It looks like your column is single-select, and you're looking to count rows with In Progress and row On Hold, is that correct? If so, you can add together two metrics for your total:

=COUNTIFS(Status:Status, "In Progress") + COUNTIFS(Status:Status, "On Hold")

Cheers,

Genevieve

NickBlocker

Hi @Genevieve P.

That worked like a charm!!! Below is my end formula :)

=COUNTIFS(Status:Status, "In Progress") + COUNTIFS(Status:Status, "On Hold") + COUNTIFS(Status:Status, "Clarification") + COUNTIFS(Status:Status, "Pending Assignment") + COUNTIFS(Status:Status, "Possible Risk") + COUNTIFS(Status:Status, "Late")