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 |
#11
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|