Multiple IFs combined with Countm formula

Evan Summerville

Greetings,

I'm currently attempting to write a formula to provide variable outputs based on a Countm function(column in my smartsheet that has multiple dropdown options).

What I'm looking to do is have a single formula that will take the result of a Countm on my "corporate objectives" and output a percentage. In the example below, I have 6 different objectives of which I've roughly defined logic for.

Basically, if the countm finds a single entry in corporate objectives the output is one, if there are two entries the output is .5 and so on all the way to six.

Note: Ideally if there are zero entries found from the countm the output is 0

Essentially it boils down to the following statements(which I'd like to combine):

=IF(COUNTM([Corporate Objectives]:[Corporate Objectives]=1, "1", 0)

=IF(COUNTM([Corporate Objectives]:[Corporate Objectives]=2, ".5", 0)

=IF(COUNTM([Corporate Objectives]:[Corporate Objectives]=3, ".33", 0)

=IF(COUNTM([Corporate Objectives]:[Corporate Objectives]=4, ".25", 0)

=IF(COUNTM([Corporate Objectives]:[Corporate Objectives]=5, ".2", 0)

=IF(COUNTM([Corporate Objectives]:[Corporate Objectives]=6, ".17", 0)

Is anyone aware of how I could combine this logic into a single formula?

Let me know what other info I could provide or questions I could answer.

Thanks

Paul Newcome

This would be a case of nested IF statements. To nest IF statements, you basically drop the second IF into the 3rd portion of the first IF. The third IF goes into the 3rd portion of the second IF. The fourth IF goes into the 3rd portion of the third IF, so on and so forth.

Basically, replace the 0 in the first with the second. Then replace the 0 in the second with the third. Replace the 0 in the third with the fourth, etc....

=IF(COUNTM([Corporate Objectives]:[Corporate Objectives]=1, "1", IF(COUNTM([Corporate Objectives]:[Corporate Objectives]=2, ".5", IF(COUNTM([Corporate Objectives]:[Corporate Objectives]=3, ".33", IF(COUNTM([Corporate Objectives]:[Corporate Objectives]=4, ".25", IF(COUNTM([Corporate Objectives]:[Corporate Objectives]=5, ".2", IF(COUNTM([Corporate Objectives]:[Corporate Objectives]=6, ".17", 0)))))

Evan Summerville

Hey Paul,

Thanks for the help, I think I'm about half way there with this formula:

=IF(COUNTM([Corporate Objectives]:[Corporate Objectives]=1), "1", IF(COUNTM([Corporate Objectives]:[Corporate Objectives]=2), ".5", IF(COUNTM([Corporate Objectives]:[Corporate Objectives]=3), ".33", IF(COUNTM([Corporate Objectives]:[Corporate Objectives]=4), ".25", IF(COUNTM([Corporate Objectives]:[Corporate Objectives]=5), ".2", IF(COUNTM([Corporate Objectives]:[Corporate Objectives]=6), ".17", 0)))))

Two challenges remain:

1. The formula above returns "1" no matter what (countm function seems to be somewhat ignored)
2. I'd like to apply this as a column formula, so is there a way to add an @row or @cell to each of those so I can have outputs per row in my spreadsheet?

Thanks!

Paul Newcome

My apologies. I just noticed that your COUNTM functions aren't closed out.

=IF(COUNTM([Column Name]:[Column Name]) = 1

You have:

=IF(COUNTM([Column Name]:[Column Name] = 1)

The closing parenthesis should be immediately after the range.