Forum Discussion

Copper Contributor

Sep 01, 2017

Solved

Using SUM to total values produced from IF statements

I am producing a questionnaire that calculates a score. Answers to questions add different numbers of points to the score. Part of the questionnaire is Y/N answers, but others have a rating of 1, 2, ...

Excel on Mac

office 365

Lorenzo
Sep 05, 2017
Cathe
Your C column shows a green triangle on the top left corner of each cell. This probably indicates the values in this column are stored as Text values (vs. Number).

Your IF formula is OK but as it takes the value from column C if the later is a Text value then in returns that Text value and logically SUM ignores it.

Several options: Convert the values in column C to Number values. If you can't change your formula as follow: =IF(E24="y",C24*1,0) =IF(E24="y",--C24,0). This will not change the value in column C but will convert it to a Number value in column F

Lorenzo

Silver Contributor

Sep 05, 2017

Cathe

Your C column shows a green triangle on the top left corner of each cell. This probably indicates the values in this column are stored as Text values (vs. Number).

Your IF formula is OK but as it takes the value from column C if the later is a Text value then in returns that Text value and logically SUM ignores it.

Several options: Convert the values in column C to Number values. If you can't change your formula as follow: =IF(E24="y",C24*1,0) =IF(E24="y",--C24,0). This will not change the value in column C but will convert it to a Number value in column F

LL444L

Copper Contributor

Apr 12, 2022

I was struggling with converting this formula from text to number answer: =IF(F13=1,"15",IF(F13=2,"12",IF(F13=3,"9",IF(F13=4,"6",IF(F13=5,"3",IF(F13=6,"0"))))))
I ended up inserting an adjacent new column to convert the answer to a number using your advice. =G13*1 Then I hid the column with the IF statements to avoid confusion on the answer to use in future calculations. It worked!! Whew - it's a big file.

SergeiBaklan
Diamond Contributor
Apr 15, 2022
LL444L

As variant

=3*(6-F13)
Lorenzo
Silver Contributor
Apr 12, 2022
LL444L

Your formula can be "simplified" as:
=IF(F13=1,15,IF(F13=2,12,IF(F13=3,9,IF(F13=4,6,IF(F13=5,3,IF(F13=6,0))))))
in which case you shouldn't need an adjacent column with =G13*1

If you run Excel > 2016 Or 365, 2 alternatives:
=IFS(F13=1,15, F13=2,12, F13=3,9, F13=4,6, F13=5,3, F13=6,0, TRUE,"Unknown")
=SWITCH(F13, 1,15, 2,12, 3,9, 4,6, 5,3, 6,0, "Unknown")