Summing conditions over muti-worksheets.

lbgolferga

New Member
Joined
Mar 5, 2002
Messages
5
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?
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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

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.
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top