SUM more results from more IF conditions

Hi All,

image different questions (1 for each column) with 3 possible drop-down answers (Yes, Maybe, Not). Every combination question/answer generate a different score.

Ex: Question 1

Y - 3 points
M - 5 points
N - 8 points

I need to have in a single cell the sum of the score of a gruop of question.

Have you any tips or suggestion for me?

Many thanks in advance,

Fabio

Find more posts tagged with

Human Resources

Formulas

Comments

Andrée Starå

Hi Fabio,

Can you share some screenshots or other helpful information?

I hope this helps you!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

It seems as if a simple SUM function should work for you once you designate the ranges to sum since you only want to sum a group regardless of the answer.

L_123

=countif(A:A,"Y") * 3 + countif(A:A,"M") * 5 + countif(A:A,"N")*8

Where the column name is A you can have different columns or whatever, just change it to suit your needs. if you want a by row solution you need if statements

Mike Wilday

@Luke. Seems like you could use your same formula but adjusted to shoot across the row. You would just need to assign the row number to the range and drag fill it down to calculate for each row. Assuming your column names were Column One, Column Two and Column There, you could use the following.

=COUNTIFS([Column One]@row:[Column Three]@row, "Y") * 3 + COUNTIFS([Column One]@row:[Column Three]@row, "M") * 5 + COUNTIFS([Column One]@row:[Column Three]@row, "N") * 8

This worked for me. You could have as many columns as you wanted, you just need to make sure that you list the first and last one in the series...

@fabio

Dear All,

thanks to your helpful indications I solve this issue.

This is the correct formula for me:

=COUNTIFS([Column One]2; "Y") * 3 + COUNTIFS([Column One]2; "M") * 5 + COUNTIFS([Column One]2; "N") * 7 + COUNTIFS([Column Two]2; "Y") * 1 + COUNTIFS([Column Two]2; "M") * 4 + COUNTIFS([Column Two]2; "N") * 9 + COUNTIFS([Column Three]2; "Y") * 3 + COUNTIFS([Column Three]2; "M") * 3 + COUNTIFS([Column Three]2; "N") * 2

Infact for each question (or column) scores are different, so I need to list all the possibility in the formula and I can't use @row.

Thanck you again for the support!

All the best,

Fabio Calandri

Mike Wilday

Gotcha, that makes sense. You have different point values per column. You could still use @row as it will pull the row number for you. @row just pulls the row number that your formula is in... So if you aren't putting the tally in the same row, then you're right. Otherwise, if your tally is in the same row @row can replace the 2 so its easier to copy and paste into other rows.

Sign in to join the conversation!

Quick Links

Trending discussions

Dropdown list restrictions
Is there a way to limit a dropdown list so that the values in the dropdown list can only be used once in a column?
seeking VLOOKUP or equivalent, search for most recent value, multiple dropdowns
My product/use case: I seek to build a camera inventory. Each camera has a unique identifier. Cameras are mobilized to different locations on different days. I need help making a VLOOKUP or equivalent combination of formulae functions to run 2 queries cross-referencing 2 grids, searching a column with multiple dropdown…
Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet