A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Sum a column that contains if statement that reference cells with



 
 
Thread Tools Display Modes
  #11  
Old February 18th, 2009, 02:29 AM posted to microsoft.public.excel.worksheet.functions
CurlyDave
external usenet poster
 
Posts: 112
Default Sum a column that contains if statement that reference cells w

On Feb 17, 6:05*am, abusymomforever
wrote:
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"
--
abusymomforever

"CurlyDave" wrote:
On Feb 16, 9:43 pm, abusymomforever
wrote:
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


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


You still have quotes in the formula??!!
  #12  
Old February 24th, 2009, 03:31 PM posted to microsoft.public.excel.worksheet.functions
abusymomforever
external usenet poster
 
Posts: 9
Default Sum a column that contains if statement that reference cells w

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


"JE McGimpsey" wrote:

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.




In article ,
abusymomforever wrote:

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


  #13  
Old February 24th, 2009, 03:32 PM posted to microsoft.public.excel.worksheet.functions
abusymomforever
external usenet poster
 
Posts: 9
Default Sum a column that contains if statement that reference cells w

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

Thanks
--
abusymomforever


"T. Valko" wrote:

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".

--
Biff
Microsoft Excel MVP


"abusymomforever" wrote in
message ...
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




  #14  
Old February 24th, 2009, 07:17 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default Sum a column that contains if statement that reference cells w

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


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

Thanks
--
abusymomforever


"T. Valko" wrote:

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".

--
Biff
Microsoft Excel MVP


"abusymomforever" wrote in
message ...
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






 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:49 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.