I'm having problems getting the following to work:

=SUMPRODUCT(--(INDIRECT($AW$3&"!$O$1:$O$6000")="XN01"),--(INDIRECT($AW$3&"!$BL$1:$BL$6000")={"A","B"}),(IND IRECT($AW$3&"!$CI$1:$CI$6000")))

The formula works when I only use one criteria in the range for $BL i.e.
{"A"}, but as soon as I add more than one criteria in, I get an error.

Thanks

Hi,
try
=SUMPRODUCT(--(INDIRECT($AW$3&"!$O$1:$O$6000")="XN01"),--(INDIRECT($AW$3&"!$BL$1:$BL$6000")="A"),--(INDIRECT($AW$3&"!$BL$1:$BL$6000")="B"),(INDIRECT( $AW$3&"!$CI$1:$CI$6000")))

"JPDS" wrote:

I'm having problems getting the following to work:

=SUMPRODUCT(--(INDIRECT($AW$3&"!$O$1:$O$6000")="XN01"),--(INDIRECT($AW$3&"!$BL$1:$BL$6000")={"A","B"}),(IND IRECT($AW$3&"!$CI$1:$CI$6000")))

The formula works when I only use one criteria in the range for $BL i.e.
{"A"}, but as soon as I add more than one criteria in, I get an error.

Thanks

Hi,

Try this

=SUMPRODUCT((INDIRECT($AW$3&"!$O$1:$O$6000")="XN01 ")*(INDIRECT($AW$3&"!$BL$1:$BL$6000")="A"+(INDIREC T($AW$3&"!$BL$1:$BL$6000")="B"))*(INDIRECT($AW$3&" !$CI$1:$CI$6000")))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"JPDS" wrote in message
...
I'm having problems getting the following to work:

=SUMPRODUCT(--(INDIRECT($AW$3&"!$O$1:$O$6000")="XN01"),--(INDIRECT($AW$3&"!$BL$1:$BL$6000")={"A","B"}),(IND IRECT($AW$3&"!$CI$1:$CI$6000")))

The formula works when I only use one criteria in the range for $BL i.e.
{"A"}, but as soon as I add more than one criteria in, I get an error.

Thanks

Try using a defined name for your ranges. Then
=SUMPRODUCT(--(rngO="XN01"),--(rngBL={"A","B"}),rngCL)
or
=SUMPRODUCT((rngO="XN01")*(rngBL={"A","B"})*rngCL)


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JPDS" wrote in message
...
I'm having problems getting the following to work:

=SUMPRODUCT(--(INDIRECT($AW$3&"!$O$1:$O$6000")="XN01"),--(INDIRECT($AW$3&"!$BL$1:$BL$6000")={"A","B"}),(IND IRECT($AW$3&"!$CI$1:$CI$6000")))

The formula works when I only use one criteria in the range for $BL i.e.
{"A"}, but as soon as I add more than one criteria in, I get an error.

Thanks

Would it matter if I told you that a person is an "A" OR "B"? Does your
calculation now try and determine if the person is both which is why it
brings back a zero?

"Ashish Mathur" wrote:

Hi,

Try this

=SUMPRODUCT((INDIRECT($AW$3&"!$O$1:$O$6000")="XN01 ")*(INDIRECT($AW$3&"!$BL$1:$BL$6000")="A"+(INDIREC T($AW$3&"!$BL$1:$BL$6000")="B"))*(INDIRECT($AW$3&" !$CI$1:$CI$6000")))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"JPDS" wrote in message
...
I'm having problems getting the following to work:

=SUMPRODUCT(--(INDIRECT($AW$3&"!$O$1:$O$6000")="XN01"),--(INDIRECT($AW$3&"!$BL$1:$BL$6000")={"A","B"}),(IND IRECT($AW$3&"!$CI$1:$CI$6000")))

The formula works when I only use one criteria in the range for $BL i.e.
{"A"}, but as soon as I add more than one criteria in, I get an error.

Thanks

Try

=SUMPRODUCT((INDIRECT($AW$3&"!$O$1:$O$6000")="XN01 ")
*(INDIRECT($AW$3&"!$BL$1:$BL$6000")={"A","B"})
*(INDIRECT($AW$3&"!$CI$1:$CI$6000")))

---
HTH

Bob Phillips

"JPDS" wrote in message
...
I'm having problems getting the following to work:

=SUMPRODUCT(--(INDIRECT($AW$3&"!$O$1:$O$6000")="XN01"),--(INDIRECT($AW$3&"!$BL$1:$BL$6000")={"A","B"}),(IND IRECT($AW$3&"!$CI$1:$CI$6000")))

The formula works when I only use one criteria in the range for $BL i.e.
{"A"}, but as soon as I add more than one criteria in, I get an error.

Thanks

HI Don, I cant use a range because I reference multiple monthly sheets which
is determined by the '$AW$3' part. Its getting quite tricky!

"Don Guillett" wrote:

Try using a defined name for your ranges. Then
=SUMPRODUCT(--(rngO="XN01"),--(rngBL={"A","B"}),rngCL)
or
=SUMPRODUCT((rngO="XN01")*(rngBL={"A","B"})*rngCL)


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JPDS" wrote in message
...
I'm having problems getting the following to work:

=SUMPRODUCT(--(INDIRECT($AW$3&"!$O$1:$O$6000")="XN01"),--(INDIRECT($AW$3&"!$BL$1:$BL$6000")={"A","B"}),(IND IRECT($AW$3&"!$CI$1:$CI$6000")))

The formula works when I only use one criteria in the range for $BL i.e.
{"A"}, but as soon as I add more than one criteria in, I get an error.

Thanks

.

Hi,

My formula works on OR (as desired). The + in the sumproduct implies an OR
condition. Could you kindly recheck

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"JPDS" wrote in message
...
HI Don, I cant use a range because I reference multiple monthly sheets
which
is determined by the '$AW$3' part. Its getting quite tricky!

"Don Guillett" wrote:

Try using a defined name for your ranges. Then
=SUMPRODUCT(--(rngO="XN01"),--(rngBL={"A","B"}),rngCL)
or
=SUMPRODUCT((rngO="XN01")*(rngBL={"A","B"})*rngCL)


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JPDS" wrote in message
...
I'm having problems getting the following to work:

=SUMPRODUCT(--(INDIRECT($AW$3&"!$O$1:$O$6000")="XN01"),--(INDIRECT($AW$3&"!$BL$1:$BL$6000")={"A","B"}),(IND IRECT($AW$3&"!$CI$1:$CI$6000")))

The formula works when I only use one criteria in the range for $BL
i.e.
{"A"}, but as soon as I add more than one criteria in, I get an error.

Thanks

.