why is there a { in my formula and why wont it work?

[Gorgsey]

Evening,

I've taken over a membership spreadsheet for a club. Part of the
spreadsheet ("report" sheet) produces a break down of the membership
dependant on where they work, splitting it further to the type of work. I've
noticed that the "report" has missed a couple of trades, so I copied the
formula and changed the text to check for. The formula is as follows:

=SUM(IF(List!$K$3:$K517="shop",IF(List!$F$3:$F517="assistant",1,0)))

I want to sum the people that work in the shop but are cleaners, so I
changed assistant to cleaners, and although there are 3 in the list the
formula displays a zero. If I view the formula by pressing the "insert
function" button it says it should return a 3 but still displays a zero when
I close insert function page. One thing I have noticed is that all the
formulas in the "report" sheet have { at the start (before the = sign) and }
at the end. When I put these on the copied formula it displays the whole
formula in the cell rather than the result of the formula.

The spreadsheet is something I've inherited so any help would be appreciated.

Many thanks.

 

[Alan]

It's an array formula and it needs to be entered with Ctrl > Shift > Enter.
Click the formula in the Formula Bar and press all three keys at the same
time. The result should look like this,
{=SUM(IF(List!$K$3:$K517="shop",IF(List!$F$3:$F517="assistant",1,0)))}
Array formulas can only be entered this way, you can't put the {}'s in
yourself,
Regards,
Alan.

 

[Dave Peterson]

If I have a choice between using a formula that needs to be array entered
(ctrl-shift-enter, like Alan explained) and a formula that just needs to be
entered normally, I'll choose the normal enter.

=sumproduct(--(list!$k$3:$k$517="shop"),--(list!$f$3:$f$517="assistant"))

(or if you're using xl2007, look at =countifs() in excel's help.)

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[FSt1]

hi,
i agree with dave meaning there is more than one way to get from here to
there and personally, i don't like array formulas either. but sometime they
do come in handy. rare by my experiece.
but i also agree that Alan's responce may not really cover array formulas.
(no offence allen)
see this site for a more detailed explination of array formulas. this site
may not cover all, but it may provide more understanding of array formulas.
http://www.cpearson.com/excel/ArrayFormulas.aspx

regards
FSt1

 

[Alan]

'but i also agree that Alan's responce may not really cover array formulas.
(no offence allen)'

No offence taken, but agree with who?

Regards, Alan

 

[T. Valko]

When you see those squiggly brackets { } around a formula it means that that
formula is an array formula.

Array formulas are entered differently than a regular formula. After you
type in a regular formula you hit the ENTER key. With an array formula you
*must* use a combination of keys. Those keys are the CTRL key, the SHIFT key
and the ENTER key. That is, hold down both the CTRL key and the SHIFT key
then hit the ENTER key.

When done properly Excel will enclose the formula in the squiggly brackets
{ }. You can't just type these brackets in, you *must* use the key combo to
produce them. Also, anytime you edit an array formula it *must* be
re-entered as an array using the key combo.

 

[FSt1]

sorry to be confusing. i may have been agreeing with me or perhaps i chose a
bad way to express myself.. you did explain how to enter an array formula and
were very correct but the OP what wondering just what these brackets were and
what they were for(althought not stated directly) . you, dave and T.Valko(who
may have posted after i did) didn't seem to address the what and why about an
array formula. so i directed the op to a sited that might give some
understanding about array formulas and not just how to enter them. as stated,
i don't like them but do admit that they can be handy at time.
again, sorry for any confusion.

regards
FSt1