See if this helps:

http://xldynamic.com/source/xld.SUMPRODUCT.html

If not, just let me know and I'll give you a "deluxe" explanation!

--
Biff
Microsoft Excel MVP


"JPDS" wrote in message
...
That works perfectly, now I have the arduous task of understanding why
your
formula works and mine doesnt! What was wrong with my formula so I can
understand it a bit more?

Thanks again

"T. Valko" wrote:

Try it like this...

=SUMPRODUCT(--(ISNUMBER(MATCH(INDIRECT($AW$3&"!BL1:BL6000"),{"A" ,"B"},0))),--(ISNUMBER(MATCH(INDIRECT($AW$3&"!O1:O6000"),{"XN01 ","XR01"},0))),INDIRECT($AW$3&"!CI1:CI6000"))

--
Biff
Microsoft Excel MVP


"JPDS" wrote in message
...
The following formula works fine:
=SUMPRODUCT(--(INDIRECT($AW$3&"!$BL1:$BL$6000")={"A"}),--(INDIRECT($AW$3&"!$O$1:$O$6000")="XN01"),(INDIRECT ($AW$3&"!$CI$1:$CI$6000")))

However, I cant seem to get the following to work:
=SUMPRODUCT(--(INDIRECT($AW$3&"!$BL1:$BL$6000")={"A","B"}),--(INDIRECT($AW$3&"!$O$1:$O$6000")="XN01","XR01"),(I NDIRECT($AW$3&"!$CI$1:$CI$6000")))

I need to be able to summarise (using a headcount indicator (1) in
Column
CI) groups of people together who are in certain groups e.g. staff can
be
in
groups A,B or C in Column BL, and in groups XN01 or XR01 in column O.

The indirect function is used as there are monthly named sheets with
similar
data in.

Thanks




.