Can anyone tell me why the following doesnt work?

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

where XN01 is a staff salary band
$CI:$CI is a headcount figures containing 1's and 0's
$BL is an Ethnicity grouping which I need to group up e.g. A = white
english, B = white welsh. I am basically summing the headcount of white
people at band XN01.

Some of the problems I am having is that if I leave just one criteria for
ethnicity in e.g. "A", then the formula works. I then have to recreat the
calculation for bands XN01-XN09, with twelve other groupings of ethnicity so
I need to group many codes together.
Thanks

See my earlier post

HTH

Bob

"JPDS" wrote in message
...
Can anyone tell me why the following doesnt work?

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

where XN01 is a staff salary band
$CI:$CI is a headcount figures containing 1's and 0's
$BL is an Ethnicity grouping which I need to group up e.g. A = white
english, B = white welsh. I am basically summing the headcount of white
people at band XN01.

Some of the problems I am having is that if I leave just one criteria for
ethnicity in e.g. "A", then the formula works. I then have to recreat the
calculation for bands XN01-XN09, with twelve other groupings of ethnicity
so
I need to group many codes together.
Thanks

Hi Bob,

Ive tried your formula in a blank sheet and it works fine. I just cant get
it to work in my sheet. Is it worth me sending you a copy? Maybe i'm trying
to get it to do something which it cant do.

"Bob Phillips" wrote:

See my earlier post

HTH

Bob

"JPDS" wrote in message
...
Can anyone tell me why the following doesnt work?

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

where XN01 is a staff salary band
$CI:$CI is a headcount figures containing 1's and 0's
$BL is an Ethnicity grouping which I need to group up e.g. A = white
english, B = white welsh. I am basically summing the headcount of white
people at band XN01.

Some of the problems I am having is that if I leave just one criteria for
ethnicity in e.g. "A", then the formula works. I then have to recreat the
calculation for bands XN01-XN09, with twelve other groupings of ethnicity
so
I need to group many codes together.
Thanks


.

Yeah sure, mail it to Bob dot NGs at gmail dot com (do the obvious)

Bob


"JPDS" wrote in message
...
Hi Bob,

Ive tried your formula in a blank sheet and it works fine. I just cant get
it to work in my sheet. Is it worth me sending you a copy? Maybe i'm
trying
to get it to do something which it cant do.

"Bob Phillips" wrote:

See my earlier post

HTH

Bob

"JPDS" wrote in message
...
Can anyone tell me why the following doesnt work?

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

where XN01 is a staff salary band
$CI:$CI is a headcount figures containing 1's and 0's
$BL is an Ethnicity grouping which I need to group up e.g. A = white
english, B = white welsh. I am basically summing the headcount of white
people at band XN01.

Some of the problems I am having is that if I leave just one criteria
for
ethnicity in e.g. "A", then the formula works. I then have to recreat
the
calculation for bands XN01-XN09, with twelve other groupings of
ethnicity
so
I need to group many codes together.
Thanks


.