Formula for text columns convert to sum?

Hello,

I'm trying to help build a sort of prioritization scorecard into a sheet. We have 4 columns, each with possible values of (example) "A", "B" or "C", and would like to have another column that sums each based on assigning a number value to each dropdown option.

So for example, the columns and their possible dropdowns:

Column 1 - A, B, C

Column 2 - A, B, C

Column 3 - A, B, C

Column 4 - A, B, C

Where A = 1, B = 2, C = 3

IF Column 1 = A, Column 2 = B, Column 3 = A, and Column 4 = C, the total value presented would be 7 (1 + 2 + 1 + 3).

I'm aware we could add 4 additional columns to present the numerical value attached to each, then a 5th column with the sum of those. However, I'll be frank: this is a monster sheet and adding even more columns when I'm hoping for a "simple" formula makes me want to cry.

Any suggestions? From what I'm reading on other forum posts I *think* this is impossible but would really appreciate a confirmation. Thanks!

Find more posts tagged with

Formulas

Comments

Brian W

Are the 4 columns adjacent? You could add multiple COUNTIF statements together:

=COUNTIF([Column 1]@row:[Column 4]@row, "A") + (COUNTIF([Column 1]@row:[Column 4]@row, "B") * 2) + (COUNTIF([Column 1]@row:[Column 4]@row, "C") * 3)

Brian W

I suppose the columns wouldn’t even need to be adjacent as long as the columns in between don’t contain just A,B, or C.

JLC

Hi Brian, thanks for your help but I'm thinking we crossed wires somewhere.

Let me flesh out the information a bit. So here's what the columns are actually representing:

Scorecard criteria 1:

- Low

- Medium

- High

Scorecard criteria 2:

- Low

- Medium

- High

Scorecard criteria 3:

- Low

- Medium

- High

Scorecard criteria 4:

- Low

- Medium

- High

For all columns:

- Low = 1

- Medium = 3

- High = 5

So, as an example of a real-world use:

Scorecard criteria 1: Low (((1)))

Scorecard criteria 2: High (((5)))

Scorecard criteria 3: High (((5)))

Scorecard criteria 4: Medium (((3)))

The formula in a fifth column would return a total of "14" (since there was one "Low", two "High"s, and one "Medium").

Does that make more sense?

Brian W

It sounds like this formula should work for you, unless there’s something I’m missing:

=COUNTIF([Column 1]@row:[Column 4]@row, "Low") + (COUNTIF([Column 1]@row:[Column 4]@row, "Medium") * 3) + (COUNTIF([Column 1]@row:[Column 4]@row, "High") * 5)

This formula assumes that your criteria columns are named Column 1, Column 2, Column 3, and Column 4. The formula searches all the cells in a row between Column 1 and Column 4 and counts the instances of Low, Medium, High, then multiplies those counts by 1, 3, and 5 respectively and adds them together.

JLC

Way to go Brian!! That's exactly what we were looking for. Thank you so much for your help, much appreciated!!

JLC

Sorry Brian, in my excitement at your perfect answer I forgot to ask one more add-on. This isn't something we're implementing on Day 1 so it's okay if we can't figure it out right now. But after the countif, is there a way of populating (instead of the numerical value) a word? So, for example, if the overall score comes back as between 0 and 10, the value shown in the cell should be something like "Low importance". If it's between 10-20, it should read "Medium importance", etc. What's the framework for adding something like that if at all? Many thanks again.

Brian W

Sure. You could use the formula in a nested IF formula:

=IF(COUNTIF([Column 1]@row:[Column 4]@row, "Low") + (COUNTIF([Column 1]@row:[Column 4]@row, "Medium") * 3) + (COUNTIF([Column 1]@row:[Column 4]@row, "High") * 5) >= 20, "High Importance", IF(COUNTIF([Column 1]@row:[Column 4]@row, "Low") + (COUNTIF([Column 1]@row:[Column 4]@row, "Medium") * 3) + (COUNTIF([Column 1]@row:[Column 4]@row, "High") * 5) >= 10, "Medium Importance", IF(COUNTIF([Column 1]@row:[Column 4]@row, "Low") + (COUNTIF([Column 1]@row:[Column 4]@row, "Medium") * 3) + (COUNTIF([Column 1]@row:[Column 4]@row, "High") * 5) > 0, "Low Importance")))

In this version, it returns “High Importance” if the count is 20+, "Medium Importance” if 10-19, or "Low Importance” for 1-9.

Sign in to join the conversation!

Quick Links

Trending discussions

How to use previous cell value (from cell history) in a formula?
I have seen this question asked multiple times in the forum, but I couldn't find any clear answer provided in the answers. Please excuse me if a solution already exists, but if not, will someone please let me know if it is possible to use a previous cell value (from cell history) in a formula? I am specifically trying to…
Automation not triggering when % Complete shows '100% instead of 100%
I have an automation set up so that when the Status column changes to Complete, Canceled, or Not Needed, Smartsheet automatically updates the % Complete column to 100% (Text/Number column type). After that, another workflow should trigger to alert the next person that their task is ready. This worked fine before, but…
Sheet Countdown Timer
Happy Thursday. I know many have asked this question before, but does anyone know of a way to have a column actively countdown time? Even if a plugin is needed, it will be considered. Here is what I'm looking to do. When a form is filled out and a new line is added to the sheet, I'd like the "Countdown Timer" column to…