Welcome!

Help with a formula

Greetings,

First time posting because I'm not well versed with formulas and I'm hoping that someone smarter than me can offer some guidance.

I would like to create a form that assigns a point value to specific activities (e.g. JTA, IDW, SS, TR and so on) identified in different columns and sums the total value of all activities in the last column. The use case is to calculate points for a rewards program. See attached image for reference.

I've tried several of the Smartsheet formulas and I cannot seem to get it right so I'm calling in the calvary for help.

Can anyone offer some insights on what I'm missing?

Thanks!

Screen Shot 2017-09-21 at 2.46.19 PM.png

Find more posts tagged with

Formulas

Comments

SmSulli

KJT,

Are all the activities the same point value?

If yes,

In the Points Accumulated you can COUNTIF and then multiply by the point value.

=(COUNTIF([Activity_1]1:[Activity_9]1, ISTEXT(@cell))) * 10

If no,

Then it's certainly more complicated. I would recommend adding a Points column for each activity column. These Points columns can be hidden once you have them setup. Then you can sum the points column in the Points Accumulated column.

Each individual Points column:

=IF([Activity_1]1 = "JTA", 10, IF([Activity_1]1 = "IDW", 20, IF([Activity_1]1 = "TR", 30, IF([Activity_1]1 = "SS", 40, IF([Activity_1]1 = "VLZ", 50, IF([Activity_1]1 = "VCDX Def", 60, IF([Activity_1]1 = "VCDX AppRev", 70, IF([Activity_1]1 = "VCDX RemDef", 80, ""))))))))

Then the Points Accumulated column:

=SUM([Points_1]1, [Points_2]1, [Points_3]1, [Point_4]1, [Points_5]1, [Points_6]1, [Points_7]1, [Points_8]1, [Points_9]1)

KJT

Cheers, SmSulli,

I have tried the solution that you presented. I created a column named Points_Activity_1 and inserted the formula from above. Unfortunately, I keep getting an "#UNPARSEABLE" error. I have checked and rechecked my syntax (below) but cannot seem to determine what I'm doing wrong:

=IF([Activity_1]1 = “JTA”, 25, IF([Activity_1]1 = “IDW”, 25, IF([Activity_1]1 = “Lab Builds”, 25, IF([Activity_1]1 = “TR”, 20, IF([Activity_1]1 = “VCDX Def”, 15, IF([Activity_1]1 = “VCDX RemDef”, 10, IF([Activity_1]1 = “VCDX AppRev”, 10, IF([Activity_1]1 = “VLZ Short”, 10, IF([Activity_1]1 = “VMW Blogging”, 10, IF([Activity_1]1 = “VLZ Series”, 20, IF([Activity_1]1 = “SS”, 10, IF([Activity_1]1 = “Lab Testing”, 10, IF([Activity_1]1 = “Blog CLS”, 1, “”)))))))))))))

The IF statement help page is not much help either. Any thoughts on what I might be doing wrong?

Screen Shot 2017-09-22 at 11.32.26 AM.png

SmSulli

KJT,

I copied your formula and have the same problem.

I have no explanation for this, but the quotation marks are no good. Some sort of font issue? When I change all the quotes in my Smartsheet, it works.

There's a lot of quotes, try it with a shortened version of the formula first.

Good Luck

Shawn