Excel 2010 - Return either "NA", 0 or Calculate

**jacktiser** · 03-22-2012, 04:32 AM

I want Excel to return the Sum on B4 as follows: -

- If B1 = "NA", B2 ="NA" ,B3 = "NA", then return "NA"
- If B1 = 120, B2 = 0, B3 = "NA", then return 120
- If B1 = "NA", B2 = 0, B3 = "NA", then return 0

Can somebody out there help?

**darknation144** · 03-22-2012, 04:55 AM

The ones that are producing N/A, why don't you surround the formula with iferror() then you won't have the n/a and sum will work perfectly.

**kvsrinivasamurthy** · 03-22-2012, 05:05 AM

Try this

=IF(B3="NA",IF(B1="NA",IF(B2="NA","NA",IF(B2=0,0,"")),IF(B1=120,120,"")),"")

**jacktiser** · 03-22-2012, 07:08 AM

I'm still stuck. All solutions provided thus far do not work. Thanks and let's keep on trying.

**jacktiser** · 03-26-2012, 06:46 AM

I got my formula,

=IF(COUNTIF(B1:B3,"NA")=3,"NA",SUM(B1:B3))

May I take this very opportinity to thank Excel MVP, Bob Umlas for figuring out the formula for me. Bob, your assistance is highly appreciated.

To all of you who tried to assist on this query, thanks to a positive infinity!