Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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(D2:D400,"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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif formulas change after doing a sort | Excel Worksheet Functions | |||
How do I use wild cards in nested array formulas? | Excel Worksheet Functions | |||
Array Formulas take waaaay too long... | Excel Worksheet Functions | |||
nested formulas | Excel Worksheet Functions | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions |