|
| 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. |
|
|||||||
|
|
|
Thread Tools | Display Modes |
|
#1
February 17th, 2009, 04:43 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum a column that contains if statement that reference cells with
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 
|
|
#2
February 17th, 2009, 04:49 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum a column that contains if statement that reference cells with
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
|
|
#3
February 17th, 2009, 04:53 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum a column that contains if statement that reference cells with
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
|
|
#4
February 17th, 2009, 04:56 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum a column that contains if statement that reference cells with
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
|
|
#5
February 17th, 2009, 06:33 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum a column that contains if statement that reference cells with
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. -- If this helps, please click the Yes button Cheers, Shane Devenshire "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
|
|
#6
February 17th, 2009, 12:38 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum a column that contains if statement that reference cells w
I have already tried that too. Did not work.
Thanks, though. -- 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
|
|
#7
February 17th, 2009, 12:40 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum a column that contains if statement that reference cells w
I have tried that already, also with no luck. But I will try it again.
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
|
|
#8
February 17th, 2009, 12:41 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum a column that contains if statement that reference cells w
I am trying to get a numeric sum of column "B" but my reference cells are in
column "A" which contain TEXT. -- 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
|
|
#9
February 17th, 2009, 01:05 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum a column that contains if statement that reference cells w
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
|
|
#10
February 17th, 2009, 06:42 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum a column that contains if statement that reference cells w
**NEVER** format cells as TEXT that will contain numbers.
Try this for your sum formula: =SUMPRODUCT(--(B1:B10)) Adjust the range to suit. -- Biff Microsoft Excel MVP "abusymomforever" wrote in message ... I am trying to get a numeric sum of column "B" but my reference cells are in column "A" which contain TEXT. -- 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 | |
|
|
Linear Mode
