| Home |
| Search |
| Today's Posts |
|
#1
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
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
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
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
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
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
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
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
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
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using reference column # in IF Statement | Excel Discussion (Misc queries) | |||
| Can I use an IF statement to reference 2 different cells? | Excel Worksheet Functions | |||
| Can I use an IF statement to reference 2 different cells? | Excel Worksheet Functions | |||
| Can I use an IF statement to reference 2 different cells? | Excel Worksheet Functions | |||
| Can I use an IF statement to reference 2 different cells? | Excel Worksheet Functions | |||
Linear Mode
