COUNTIF Formula

Erick Gutierrez

Need assistance with the following formula:

Column labeled "CA Tier" has four categories, on the Excel spreadsheet that I'm mirroring, the formula is for CA is =COUNTIF($F:$F,S7), what is the formula that I would use on the Smartsheet?

Nic Larsen

If you are just counting total by type from a single column:

=COUNTIF([CA Tier]:[CA Tier], "CA")

=COUNTIF([CA Tier]:[CA Tier], "CA1")

=COUNTIF([CA Tier]:[CA Tier], "CA2")

=COUNTIF([CA Tier]:[CA Tier], "CA3")

Debbie Sawyer

It really depends on what is stored in column F and what is stored in cell S7 in the excel worksheet ! The screen shots don't really give that data.

The logic in your Excel formula is saying "Set the value of this cell to the number of entries from Column F that match the value in the cell S7."

Smartsheet has the same =COUNTIF(Column F from Smartsheet, whatever was in S7) formula with the same arguments, you just need to refer to the Smartsheet column that is the same as your Excel column F and point to the cell that contains the same value in your Smartsheet that was held in S7 (column S row 7) in your Excel sheet.

I hope this helps.

Kind regards

Debbie

Nic Larsen

If you are just counting total by type from a single column:

=COUNTIF([CA Tier]:[CA Tier], "CA")

=COUNTIF([CA Tier]:[CA Tier], "CA1")

=COUNTIF([CA Tier]:[CA Tier], "CA2")

=COUNTIF([CA Tier]:[CA Tier], "CA3")

Erick Gutierrez

@Nic Larsen Thank you so much! This worked perfectly.

Debbie Sawyer

@Erick Gutierrez

Hi Erick

Great to see that the formula Nic has provided works :)

I have seen in your screen shot that your CA Tier column in Smartsheet is a MultiSelect data type, please be aware that if anyone enters more than 1 value from your dropdown list then those formulas will only count the First instance entered and any subsequent ones will not be counted.

If you only want 1 value per cell in the CA Tier column, then I would recommend changing the data type to a Single Select drop down list, then the potential for errors (entering more than 1 value with the wrong one first, thus messing up your stats) will not occur.

Kind regards

Debbie