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

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

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

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?

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

## Like this thread? Share it with others