Adding numeric values derived from formulas

[JBWalker]

I'm trying to add results that derive from a formula.

Example of what I'm trying to achieve.
Column A contains fruit and then column B:I contain individual formulas to calculate what fruit, for example;

=IF(A1="Apple","1","")
=IF(A1="Pear","1","")
=IF(A1="Orange","1","")
=IF(A1="Grape","1","")

So results would look like;

Apple 1 0 0 0
Pear 0 1 0 0
Orange 0 0 1 0
Apple 1 0 0 0
Grape 0 0 0 1

Now my problem is when i add =SUM(B1:B5) i get a result of 0 instead of 2.

 

[Fluff]

You need to remove the quotes from the number, otherwise it's text

=IF(A1="Apple",1,"")

 

[sandy666]

maybe try numbers without quotes

 

[JBWalker]

That was a very simple fix. Thank you very much

 

[Fluff]

Glad to help & thanks for the feedback.

 

[JBWalker]

If i may pose an additional question.

How would i apply the SUM equation to ignore #VALUE! error?

Reason being is that I'm attempting to build the whole spreadsheet for future use, but without knowing the fruit the =IF formula has nothing to read and therefore shows error.

 

[sandy666]

use IFERROR()

IFERROR function - Microsoft Support

How to use the IFERROR function in Excel to trap and handle errors in a formula.

support.microsoft.com