SUMPRODUCT

  • Thread starter Thread starter JPDS
  • Start date Start date
J

JPDS

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"),(INDIRECT($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
 
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"))
 
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
 
Back
Top