nested formulas

[Ctrl-Alt-Del]

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

[JE McGimpsey]

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 <[email protected]>,
Ctrl-Alt-Del <[email protected]> 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.

[bpeltzer]

{} 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

[Ctrl-Alt-Del]

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