+ Reply to Thread
Results 1 to 4 of 4

SUMPRODUCT across worksheets

  1. #1
    Stephen POWELL
    Guest

    SUMPRODUCT across worksheets

    I am familiar with using SUMPRODUCT(--(criterion=range1),(range2)) to obtain
    results not possible with SUMIF. However, I would now like to apply this to,
    say, all D5 cells in many contiguous worksheets. I get error results. Can
    someone confirm that this is not possible to do? Any suggestions for a way
    around this?
    Many thanks,
    Stephen Powell

  2. #2
    Peo Sjoblom
    Guest

    RE: SUMPRODUCT across worksheets

    If you only have one criteria like in a sumif and either sum the same range
    or another range you can use this:
    First you need to create a list with ALL (not like in 3-D excel first and
    last) sheet names or if they have the same aplha name plus numbers you can do
    it as well.
    1. if they all have different names and you put all sheet names in range
    H1:H20
    and let's say you want to sum B1:B100 in all sheets where A1:A100 is criteria1
    then you can use this

    =SUMPRODUCT(SUMIF(INDIRECT("'"&$H$1:$H$20&"'!A1:A100"),"criteria1",INDIRECT("'"&$H$1:$H$20&"'!B1:B100")))


    if the names are identical text plus different numbers like Sheet1, Sheet2
    amd so one then you can use

    =SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:20"))&"'!A1:A100"),"criteria1",INDIRECT("'Sheet"&ROW(INDIRECT("1:20"))&"'!B1:B100")))

    if you need more criteria and more ranges may I recommend Morefun by Laurent
    Longre

    It's an excellent add-in that has some nice 3-D features

    http://longre.free.fr/english/

    here's a description in English


    http://www.rhdatasolutions.com/morefunc/


    Regards,

    Peo Sjoblom

    "Stephen POWELL" wrote:

    > I am familiar with using SUMPRODUCT(--(criterion=range1),(range2)) to obtain
    > results not possible with SUMIF. However, I would now like to apply this to,
    > say, all D5 cells in many contiguous worksheets. I get error results. Can
    > someone confirm that this is not possible to do? Any suggestions for a way
    > around this?
    > Many thanks,
    > Stephen Powell


  3. #3
    Stephen POWELL
    Guest

    RE: SUMPRODUCT across worksheets

    Peo:
    Thank you kindly. Your solution works nicely. I've never used INDIRECT
    before and it is not intuitively obvious to me why it works - I will have to
    research this function to understand it.
    Thanks again.
    Stephen

    "Peo Sjoblom" wrote:

    > If you only have one criteria like in a sumif and either sum the same range
    > or another range you can use this:
    > First you need to create a list with ALL (not like in 3-D excel first and
    > last) sheet names or if they have the same aplha name plus numbers you can do
    > it as well.
    > 1. if they all have different names and you put all sheet names in range
    > H1:H20
    > and let's say you want to sum B1:B100 in all sheets where A1:A100 is criteria1
    > then you can use this
    >
    > =SUMPRODUCT(SUMIF(INDIRECT("'"&$H$1:$H$20&"'!A1:A100"),"criteria1",INDIRECT("'"&$H$1:$H$20&"'!B1:B100")))
    >
    >
    > if the names are identical text plus different numbers like Sheet1, Sheet2
    > amd so one then you can use
    >
    > =SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:20"))&"'!A1:A100"),"criteria1",INDIRECT("'Sheet"&ROW(INDIRECT("1:20"))&"'!B1:B100")))
    >
    > if you need more criteria and more ranges may I recommend Morefun by Laurent
    > Longre
    >
    > It's an excellent add-in that has some nice 3-D features
    >
    > http://longre.free.fr/english/
    >
    > here's a description in English
    >
    >
    > http://www.rhdatasolutions.com/morefunc/
    >
    >
    > Regards,
    >
    > Peo Sjoblom
    >
    > "Stephen POWELL" wrote:
    >
    > > I am familiar with using SUMPRODUCT(--(criterion=range1),(range2)) to obtain
    > > results not possible with SUMIF. However, I would now like to apply this to,
    > > say, all D5 cells in many contiguous worksheets. I get error results. Can
    > > someone confirm that this is not possible to do? Any suggestions for a way
    > > around this?
    > > Many thanks,
    > > Stephen Powell


  4. #4
    Peo Sjoblom
    Guest

    Re: SUMPRODUCT across worksheets

    My pleasure, thanks for the feedback

    --
    Regards,

    Peo Sjoblom

    (No private emails please, for everyone's
    benefit keep the discussion in the newsgroup/forum)



    "Stephen POWELL" <StephenPOWELL@discussions.microsoft.com> wrote in message
    news:7D79D2A9-4409-418E-AA68-5006D26CAC36@microsoft.com...
    > Peo:
    > Thank you kindly. Your solution works nicely. I've never used INDIRECT
    > before and it is not intuitively obvious to me why it works - I will have
    > to
    > research this function to understand it.
    > Thanks again.
    > Stephen
    >
    > "Peo Sjoblom" wrote:
    >
    >> If you only have one criteria like in a sumif and either sum the same
    >> range
    >> or another range you can use this:
    >> First you need to create a list with ALL (not like in 3-D excel first and
    >> last) sheet names or if they have the same aplha name plus numbers you
    >> can do
    >> it as well.
    >> 1. if they all have different names and you put all sheet names in range
    >> H1:H20
    >> and let's say you want to sum B1:B100 in all sheets where A1:A100 is
    >> criteria1
    >> then you can use this
    >>
    >> =SUMPRODUCT(SUMIF(INDIRECT("'"&$H$1:$H$20&"'!A1:A100"),"criteria1",INDIRECT("'"&$H$1:$H$20&"'!B1:B100")))
    >>
    >>
    >> if the names are identical text plus different numbers like Sheet1,
    >> Sheet2
    >> amd so one then you can use
    >>
    >> =SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:20"))&"'!A1:A100"),"criteria1",INDIRECT("'Sheet"&ROW(INDIRECT("1:20"))&"'!B1:B100")))
    >>
    >> if you need more criteria and more ranges may I recommend Morefun by
    >> Laurent
    >> Longre
    >>
    >> It's an excellent add-in that has some nice 3-D features
    >>
    >> http://longre.free.fr/english/
    >>
    >> here's a description in English
    >>
    >>
    >> http://www.rhdatasolutions.com/morefunc/
    >>
    >>
    >> Regards,
    >>
    >> Peo Sjoblom
    >>
    >> "Stephen POWELL" wrote:
    >>
    >> > I am familiar with using SUMPRODUCT(--(criterion=range1),(range2)) to
    >> > obtain
    >> > results not possible with SUMIF. However, I would now like to apply
    >> > this to,
    >> > say, all D5 cells in many contiguous worksheets. I get error results.
    >> > Can
    >> > someone confirm that this is not possible to do? Any suggestions for a
    >> > way
    >> > around this?
    >> > Many thanks,
    >> > Stephen Powell




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1