COUNTIF Formula Help

jlheaney

I'm not sure if I'm making this more complicated than I need to...

I'm trying to build a COUNTIF formula that accounts for 2 unique values in User Satisfaction column and can have anywhere from 4 -17 unique values in Team Name.

=COUNTIFS({User Satisfaction}, "4. Very Satisfied", {Month Survey Sent}, [Column3]@row, {Year Survey Sent}, $[Column2]$2, {Team Name} OR(@cell = "Team 1", @cell = "Team 2", @cell = "Team 3", @cell = "Team 5", @cell = "Team 6", @cell = "Team 7", @cell = "Team 8", @cell = "Team 9", @cell = "Team 10")) + COUNTIFS({User Satisfaction}, "5. Extremely Satisfied", {Month Survey Sent}, [Column3]@row, {Year Survey Sent}, $[Column2]$2, {Team Name} OR(@cell = "Team 1", @cell = "Team 2", @cell = "Team 3", @cell = "Team 5", @cell = "Team 6", @cell = "Team 7", @cell = "Team 8", @cell = "Team 9", @cell = "Team 10"))

The above is returning #unparseable.

More info:

• Each team name rolls up into larger group
• User Satisfaction has 5 values:
  • 1. Not Satisfied
  • 2. Slightly Satisfied
  • 3. Satisfied
  • 4. Very Satisfied
  • 5. Extremely Satisfied

Kelly Moore

Hey

Just looking at your formula as-written, I believe you may be missing commas in both locations following {Team Name}

As a possible alternative to {Team Name}, OR(@cell = "Team 1", @cell = "Team 2", @cell = "Team 3", @cell = "Team 5", @cell = "Team 6", @cell = "Team 7", @cell = "Team 8", @cell = "Team 9", @cell = "Team 10") you could try {Team Name}, CONTAINS("Team", @cell)

cheers

Kelly Moore

Hey

Just looking at your formula as-written, I believe you may be missing commas in both locations following {Team Name}

As a possible alternative to {Team Name}, OR(@cell = "Team 1", @cell = "Team 2", @cell = "Team 3", @cell = "Team 5", @cell = "Team 6", @cell = "Team 7", @cell = "Team 8", @cell = "Team 9", @cell = "Team 10") you could try {Team Name}, CONTAINS("Team", @cell)

cheers

Dale Murphy

@jlheaney I'm not quite sure what output you want! Is it a report that counts satisfaction levels?

My default approach is a metrics sheet that becomes a grid. Every cell in that grid is a count that checks two conditions to get a result. In your case, columns are probably User Satisfaction values. Rows are team names. I would add another column for month, but you will need to repeat the block of rows to get results for each team for each month.

That sheet could use COUNTIFS to get data from your datasheet.

Is that enough to get started? I can show how to use cross-sheet ranges if you need.

Dale

jlheaney

https://community.smartsheet.com/discussion/comment/269187#Comment_269187

Thank you @KDM, sometimes it just takes another pair of eyes that are not tired!