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 he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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



Gorgsey wrote:

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.

--

Dave Peterson