Summing conditions over muti-worksheets.

[lbgolferga]

I am trying to gather and sum multiple conditions over several worksheets. I have tried this:
=SUMPRODUCT((('NVDM Direct No Maint'!S4:S30="LB")*('NVDM Direct No Maint'!F4:F30={"1-B","1-T","1-B,T","1-T,B"})+('NVDM Direct Maint '!S4:S133="LB")*('NVDM Direct Maint '!F4:F133={"1-B","1-T","1-B,T","1-T,B"})+('Distributor HPO'!S4:S4="LB")*('Distributor HPO'!F4:F4={"1-B","1-T","1-B,T","1-T,B"}))
Which always gives me a #NA error. If I shorten it to just:
=SUMPRODUCT(('NVDM Direct Maint '!S4:S133="LB")*('NVDM Direct Maint '!F4:F133={"1-B","1-T","1-B,T","1-T,B"}))
It works fine, but adding any other number of worksheets gives the #NA error. Is this not allowed?

[Aladin Akyurek]

On 2002-03-12 07:22, lbgolferga wrote:
I am trying to gather and sum multiple conditions over several worksheets. I have tried this:
=SUMPRODUCT((('NVDM Direct No Maint'!S4:S30="LB")*('NVDM Direct No Maint'!F4:F30={"1-B","1-T","1-B,T","1-T,B"})+('NVDM Direct Maint '!S4:S133="LB")*('NVDM Direct Maint '!F4:F133={"1-B","1-T","1-B,T","1-T,B"})+('Distributor HPO'!S4:S4="LB")*('Distributor HPO'!F4:F4={"1-B","1-T","1-B,T","1-T,B"}))
Which always gives me a #NA error. If I shorten it to just:
=SUMPRODUCT(('NVDM Direct Maint '!S4:S133="LB")*('NVDM Direct Maint '!F4:F133={"1-B","1-T","1-B,T","1-T,B"}))
It works fine, but adding any other number of worksheets gives the #NA error. Is this not allowed?

Are you trying to total the results of multiconditional sums from different worksheets?

On 2002-03-12 11:32, Aladin Akyurek wrote:

Are you trying to total the results of multiconditional sums from different worksheets?

Not exactly, I am trying to count the number of occurances of certain conditions over multiple worksheets.

[Aladin Akyurek]

On 2002-03-12 11:35, Anonymous wrote:

Not exactly, I am trying to count the number of occurances of certain conditions over multiple worksheets.

lbgolferga,

If it's totaling of multiconditional counts (I said "sums" by mistake), you need:

=SUMPRODUCT(...)+SUMPRODUCT(...)+...

If you're OR'ing over multiple sheets, as you seem to do, the ranges tested must be of equal size:

Lets say that A1:B4 in Sheet1 houses the following sample:

{"a",1;"a",2;"b",2;"a",1}

and B4:C7 in Sheet2

{"a",1;"a",8;"b",2;"a",9}

Suppose I want to count records consisting of "a" and 1 in both sheets:

The formula that follows, entered in some cell in Sheet3,

[1]

=SUMPRODUCT((Sheet1!A1:A4="a")*(Sheet1!B1:B4=1)+(Sheet2!B4:B7="a")*(Sheet2!C4:C7=1))

gives us a total count of 3.

Lets pretend that the range in Sheet2 is

B4:C6 (instead of B4:C7). Notice that the size of the B4:C6 range is smaller than the size of the range of interest in Sheet1.

The [1] adapted as

[2]

=SUMPRODUCT((Sheet1!A1:A4="a")*(Sheet1!B1:B4=1)+(Sheet2!B4:B6="a")*(Sheet2!C4:C6=1))

gives us #N/A.

Whereas

[3]

=SUMPRODUCT((Sheet1!A1:A4="a")*(Sheet1!B1:B4=1))+SUMPRODUCT((Sheet2!B4:B6="a")*(Sheet2!C4:C6=1))

will give us the correct count of 3.

Aladin

[lbgolferga]

On 2002-03-12 12:04, Aladin Akyurek wrote:

lbgolferga,

If it's totaling of multiconditional counts (I said "sums" by mistake), you need:

=SUMPRODUCT(...)+SUMPRODUCT(...)+...

If you're OR'ing over multiple sheets, as you seem to do, the ranges tested must be of equal size:

Lets say that A1:B4 in Sheet1 houses the following sample:

{"a",1;"a",2;"b",2;"a",1}

and B4:C7 in Sheet2

{"a",1;"a",8;"b",2;"a",9}

Suppose I want to count records consisting of "a" and 1 in both sheets:

The formula that follows, entered in some cell in Sheet3,

[1]

=SUMPRODUCT((Sheet1!A1:A4="a")*(Sheet1!B1:B4=1)+(Sheet2!B4:B7="a")*(Sheet2!C4:C7=1))

gives us a total count of 3.

Lets pretend that the range in Sheet2 is

B4:C6 (instead of B4:C7). Notice that the size of the B4:C6 range is smaller than the size of the range of interest in Sheet1.

The [1] adapted as

[2]

=SUMPRODUCT((Sheet1!A1:A4="a")*(Sheet1!B1:B4=1)+(Sheet2!B4:B6="a")*(Sheet2!C4:C6=1))

gives us #N/A.

Whereas

[3]

=SUMPRODUCT((Sheet1!A1:A4="a")*(Sheet1!B1:B4=1))+SUMPRODUCT((Sheet2!B4:B6="a")*(Sheet2!C4:C6=1))

will give us the correct count of 3.

Aladin

Aladin,

I have overlooked the very important fact that the ranges must be of equal size. Your analysis and proposed solution is exactly correct and corrects my error. Thank You again for shedding some light where there was only darkness.