Sum a column that contains if statement that reference cells with

[abusymomforever]

I have tried every fx, combination of functions, formulas, etc and nothing
seems to work. Here is what I have and please someone tell me how to get a
numeric sum that isn't 0! Column B is IF Statement copied down Column B
=IF(a8=1234,"268.80",IF(a8=2345,"335.80",IF(a8="N/A","0.00",IF(a8=9876,"230.00",IF(a8=1010,"245.00","0.00")))))

A B
8 1234 268.80
9 2345 335.80
9 N/A 0.00
10 9876 230.00
11 N/A 0.00
12 1010 245.00

Even with the correct number of (), changing the N/A to 0 and changing it in
the formula I still get 0 or #value or some other error message. I did try
sum(a8+a9) and that seemed to work but I have hundreds of cells to add and
that just won't be feasible.

Please HELP!!!!

abusymomforever

 

[JE McGimpsey]

Try removing the quotation marks from around your numbers.


Putting them in quotes makes XL see them as Text, not numbers.

Then use =SUM(B8:B1000) to total them.

 

[T. Valko]

Hard to tell what it is you're trying to do with this but to get your SUM
formula to work change your IF formula by removing the quotes from around
any of the numbers.

When you quote numbers Excel evaluates them as TEXT and in most cases, these
can't be summed or added without some "trickeration".

 

[CurlyDave]

I have tried every fx, combination of functions, formulas, etc and nothing
seems to work.  Here is what I have and please someone tell me how to get a
numeric sum that isn't 0! Column B is IF Statement copied down Column B
=IF(a8=1234,"268.80",IF(a8=2345,"335.80",IF(a8="N/A","0.00",IF(a8=9876,"230.00",IF(a8=1010,"245.00","0.00")))))

A                          B                    
8    1234               268.80
9    2345               335.80
9    N/A                    0.00
10  9876               230.00
11  N/A                    0.00
12  1010               245.00

Even with the correct number of (), changing the N/A to 0 and changing itin
the formula I still get 0 or #value or some other error message.  I didtry
sum(a8+a9) and that seemed to work but I have hundreds of cells to add and
that just won't be feasible.

Please HELP!!!!

abusymomforever

You don't need quotes in the formula,
=IF(A1=1234,268.8,IF(A1=2345,335.8,IF(A1="N/A",0,IF(A1=9876,230,IF
(A1=1010,245,0)))))
Using quotes makes the nubers text

 

[Shane Devenshire]

Hi,

If for any reason you don't want to remove "" around number then you can
change your formula to

=--IF(A8=1234,"268.80",IF(A8=2345,"335.80",IF(A8="N/A","0.00",IF(A8=9876,"230.00",IF(A8=1010,"245.00","0.00")))))

Personally, I think you should be using VLOOKUP for the calculation, with or
without "".

Quotes makes number text and text doesn't add up.

 

[abusymomforever]

I have already tried that too. Did not work.
Thanks, though.

 

[abusymomforever]

I have tried that already, also with no luck. But I will try it again.
Thanks

 

[abusymomforever]

I am trying to get a numeric sum of column "B" but my reference cells are in
column "A" which contain TEXT.

 

[abusymomforever]

I'm so stupid. I forgot to put the sum in my equations with my ? The entire
point of the question.
Which reads
sumIF(a8=1234,"268.80",IF(a8=2345,"335.80",IF(a8="N/A","0.00",IF(a8=9876,"230.00",IF(a8=1010,"245.00",(b8:b12),"0.00")))))))

Sum(b8:b12) returns 0
I have tried with and with out absolute value, with and without ". If I use
N/A without " then I get an error

All I want is a numeric sum other than 0 for column "B"

 

[T. Valko]

**NEVER** format cells as TEXT that will contain numbers.

Try this for your sum formula:

=SUMPRODUCT(--(B1:B10))

Adjust the range to suit.

 

[CurlyDave]

I'm so stupid.  I forgot to put the sum in my equations with my ?  The entire
point of the question.
Which reads
sumIF(a8=1234,"268.80",IF(a8=2345,"335.80",IF(a8="N/A","0.00",IF(a8=9876,"230.00",IF(a8=1010,"245.00",(b8:b12),"0.00")))))))

Sum(b8:b12) returns 0
I have tried with and with out absolute value, with and without ".  If I use
N/A without " then I get an error

All I want is a numeric sum other than 0 for column "B"

You still have quotes in the formula??!!

 

[abusymomforever]

I removed all the "" but that did not fix the problem so I additionally
changed a8=0 instead of "N/A" and that fixed the problem.

Thanks for the help

 

[abusymomforever]

I fixed the problem by removing all the "" and changing a8="N/A" to a8=0 and
that fixed the problem.

Thanks

 

[T. Valko]

Good deal. Thanks for the feedback!