Sum multiple NestedIF formulas' values

**abf826** · 08-12-2010, 12:15 PM

I'm working with nested IF formulas, and cannot figure out how to sum the end values with other nested IF end values. For example,..

Where cell A1 contains the word "Yes", B1 is assigned the value 0.
Where cell A1 contains the word "Unknown", B1 is assigned the value 1.
Where cell A1 contains the word "No", B1 is assigned the value 2.

Where cell C1 contains the word "Yes", D1 is assigned the value 0.
Where cell C1 contains the word "Unknown", D1 is assigned the value 1.
Where cell C1 contains the word "No", D1 is assigned the value 2.

I need to sum the total of B1 and D1. Since these are the results of formula's, and not just entered numbers, the SUM function does not recognize them as numbers. I'd prefer not to paste values as my values for A1 and C1 will change over time.

Any ideas on how I can calculate the Sum of two values when the values are the product of formulas (namely a Nested IF formula)?

**StephenR** · 08-12-2010, 12:18 PM

Since these are the results of formula's, and not just entered numbers, the SUM function does not recognize them as numbers.

News to me.

**DonkeyOte** · 08-12-2010, 12:37 PM

I suspect your B1 formula says:

=IF(A1="Yes","0",IF(A1="Unknown","1",IF(A1="No","2","")))

so B1 holds a text string rather than a number and SUM will ignore text strings.

You should use 0, 1 & 2 rather than "0", "1" and "2"

Depending on what happens when A1 is none of Yes, No or unknown you might find you can use =B1+D1 instead of SUM given the addition operator will coerce the text strings
(however this will error should either B1 or D1 contain a non-numeric value - like a Null)