SUMPRODUCT
 

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

SUMPRODUCT
 

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

SUMPRODUCT
 

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




.

SUMPRODUCT
 

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




.