Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Summing conditions over muti-worksheets.

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,645
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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?

  3. #3
    Guest

    Default

    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.

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,645
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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


  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •