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
|
|||
|
|||
nested formulas
I am trying to get a previously built worksheet to properly run some
formulas. We have three product types, designated "C", "P", and "M". The worksheet has a column with one of these designations entered in each cell, with the weight associated with each entry in an adjoining cell in the next column. The formula calls for the worksheet to sum the weights by product type, using a nested SUM(IF) command =SUM(IF($D$2:$D$400="C",$E$2:$E$400)), and likewise for the other two types. The problem is that this formula works great when surrounded by brackets { }, but not otherwise. Attempts to manually enter or otherwise force the brackets prove fruitless, as including the = sign hides the indication that this is a formula. Re-entering the formula in a clean cell also does no good. What am I doing wrong, and how do I fix it? Many thanks. -- Ctrl-Alt-Del signing off |
#2
|
|||
|
|||
nested formulas
The brackets are automatically added by XL when you array-enter the
formula (by holding down the CTRL and SHIFT key when you hit Enter). See Help for more info. You could avoid array-entering by using SUMIF() =SUMIF(D2400,"C",E2:E400) In article , Ctrl-Alt-Del wrote: I am trying to get a previously built worksheet to properly run some formulas. We have three product types, designated "C", "P", and "M". The worksheet has a column with one of these designations entered in each cell, with the weight associated with each entry in an adjoining cell in the next column. The formula calls for the worksheet to sum the weights by product type, using a nested SUM(IF) command =SUM(IF($D$2:$D$400="C",$E$2:$E$400)), and likewise for the other two types. The problem is that this formula works great when surrounded by brackets { }, but not otherwise. Attempts to manually enter or otherwise force the brackets prove fruitless, as including the = sign hides the indication that this is a formula. Re-entering the formula in a clean cell also does no good. What am I doing wrong, and how do I fix it? Many thanks. |
#3
|
|||
|
|||
nested formulas
{} signify an array formula, which must be committed with ctrl+shift+enter
(not just the plain old enter). In this case, however, you can just use the sumif function to do what you want: =sumif($d$2:$d$4000,"C",$e$2:$e$4000). "Ctrl-Alt-Del" wrote: I am trying to get a previously built worksheet to properly run some formulas. We have three product types, designated "C", "P", and "M". The worksheet has a column with one of these designations entered in each cell, with the weight associated with each entry in an adjoining cell in the next column. The formula calls for the worksheet to sum the weights by product type, using a nested SUM(IF) command =SUM(IF($D$2:$D$400="C",$E$2:$E$400)), and likewise for the other two types. The problem is that this formula works great when surrounded by brackets { }, but not otherwise. Attempts to manually enter or otherwise force the brackets prove fruitless, as including the = sign hides the indication that this is a formula. Re-entering the formula in a clean cell also does no good. What am I doing wrong, and how do I fix it? Many thanks. -- Ctrl-Alt-Del signing off |
#4
|
|||
|
|||
nested formulas
Thank you both. I work on the backside of the network and don't see the front
end much, anymore. Your solution worked like a charm. -- Ctrl-Alt-Del signing off "bpeltzer" wrote: {} signify an array formula, which must be committed with ctrl+shift+enter (not just the plain old enter). In this case, however, you can just use the sumif function to do what you want: =sumif($d$2:$d$4000,"C",$e$2:$e$4000). "Ctrl-Alt-Del" wrote: I am trying to get a previously built worksheet to properly run some formulas. We have three product types, designated "C", "P", and "M". The worksheet has a column with one of these designations entered in each cell, with the weight associated with each entry in an adjoining cell in the next column. The formula calls for the worksheet to sum the weights by product type, using a nested SUM(IF) command =SUM(IF($D$2:$D$400="C",$E$2:$E$400)), and likewise for the other two types. The problem is that this formula works great when surrounded by brackets { }, but not otherwise. Attempts to manually enter or otherwise force the brackets prove fruitless, as including the = sign hides the indication that this is a formula. Re-entering the formula in a clean cell also does no good. What am I doing wrong, and how do I fix it? Many thanks. -- Ctrl-Alt-Del signing off |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Excel should let me use formulas that refer to other cells w/ form | Chenopod | Worksheet Functions | 7 | May 31st, 2005 04:21 PM |
importing data and formulas into Excel | Eric | General Discussion | 2 | August 25th, 2004 12:12 AM |
Trouble with protected sheets & formulas | Peter Bernadyne | General Discussion | 6 | August 11th, 2004 02:54 PM |
formulas stored in table - help | LSH | Running & Setting Up Queries | 2 | August 6th, 2004 05:08 PM |
Relative vs. Absolute Values in Formulas | Worksheet Functions | 0 | January 22nd, 2004 11:59 PM |