Sheet Summary Formula

Maddy Menna

Hello Smartsheet World!

I have a question related to a sheet summary formula. I have a column with a drop down menu tracking facility issues. The users have the ability to select multiple issues in one submission. I am trying to count how many specific issues we are handling at a given time. For example, how many HVAC systems are down.

I've been able to create the formula to calculate each item when only one issue is selected, however I keep receiving an error message when there are several items selected.

Successful Formula :

=COUNTIF([Facility Issue]:[Facility Issue], "Cooking Equipment")

Unsuccessful Formula:

=COUNTIF([Facility Issue]:[Facility Issue], "Cooler/Freezer", "Electrical",)

Thanks for any insight!

Formula Question.docx

MarceHolzhauzen

Hi @Maddy Menna

Have you tried using HAS or CONTAINS? Here's how it works:
Has function
Contains Function

Based on the information above, I would think your formula to look something like this:

=COUNTIF([Facility Issue]:[Facility Issue], CONTAINS("Electrical", @cell))

Explanation:

• COUNTIF([Facility Issue]:[Facility Issue], CONTAINS("Electrical", @cell)):
  • [Facility Issue]:[Facility Issue]: Specifies the range to evaluate (the Facility Issue column).
  • CONTAINS("Electrical", @cell): This checks if the word "Electrical" is found within each cell in the range.
  • COUNTIF counts how many cells meet the condition of containing the word "Electrical".

This formula will return the number of cells in the Facility Issue column that include the word "Electrical".

I hope this helps.

MarceHolzhauzen

Hi @Maddy Menna

Have you tried using HAS or CONTAINS? Here's how it works:
Has function
Contains Function

Based on the information above, I would think your formula to look something like this:

=COUNTIF([Facility Issue]:[Facility Issue], CONTAINS("Electrical", @cell))

Explanation:

• COUNTIF([Facility Issue]:[Facility Issue], CONTAINS("Electrical", @cell)):
  • [Facility Issue]:[Facility Issue]: Specifies the range to evaluate (the Facility Issue column).
  • CONTAINS("Electrical", @cell): This checks if the word "Electrical" is found within each cell in the range.
  • COUNTIF counts how many cells meet the condition of containing the word "Electrical".

This formula will return the number of cells in the Facility Issue column that include the word "Electrical".

I hope this helps.

Maddy Menna

Hi Marce,

This looks closer to what I need. But how do I set it up to search for all the options whe more than one item is selected? I've tried the following and it doesn't work, it comes back inparseable.

=COUNTIF([Facility Issue]:{Facility Issue], CONTAINS("Electrical", "HVAC", "Plumbing" @cell))

MarceHolzhauzen

Hi @Maddy Menna

Are you looking to only count cells that have all 3 of those selecting or any of them?

If you're looking for one that has all 3 of them, you can maybe try

=COUNTIF([Facility Issue]:[Facility Issue], AND(CONTAINS("Electrical", @cell),CONTAINS("HVAC", @cell),CONTAINS("Plumbing", @cell)))