+ Reply to Thread
Results 1 to 13 of 13

Help with Sumproduct with Indirect

  1. #1
    JE McGimpsey
    Guest

    Re: Help with Sumproduct with Indirect

    One way:

    =SUMPRODUCT(--($E$1:$AN$1<>"Cat"),--($E$1:$AN$1<>"Dog"),$E$3:$AN$3))

    convert ranges to INDIRECTs if necessary.

    In article <[email protected]>,
    Rob <[email protected]> wrote:

    > I am currently using the following formula...
    > =SUMPRODUCT((INDIRECT("$E$1:$AN$1")<>"Cat")*INDIRECT("$E$3:$AN$3"))
    > I need to add another criterium "Dog". How do I modify?


  2. #2
    Arvi Laanemets
    Guest

    Re: Help with Sumproduct with Indirect

    Hi

    Why do you use INDIRECT here? Simply
    =SUMPRODUCT(($E$1:$AN$1<>"Cat")*($E$3:$AN$3))
    will work in same way. Even better will be
    =SUMPRODUCT(--($E$1:$AN$1<>"Cat"),($E$3:$AN$3))

    About your question - for which range will the additional criterium apply?
    When you want to sum all values in row 3 with "Dog" or "Cat" in row 1, then
    =SUMPRODUCT(--($E$1:$AN$1<>"Dog"),($E$3:$AN$3))+SUMPRODUCT(--($E$1:$AN$1<>"C
    at"),($E$3:$AN$3))
    When you want to sum all values in row 3 with "Cat" in row 1 and "Dog" in
    row 2, then
    =SUMPRODUCT(--($E$1:$AN$1<>"Cat"),--($E$2:$AN$2<>"Dog"),($E$3:$AN$3))


    Arvi Laanemets


    "Rob" <[email protected]> wrote in message
    news:[email protected]...
    > Hi
    > I am currently using the following formula...
    > =SUMPRODUCT((INDIRECT("$E$1:$AN$1")<>"Cat")*INDIRECT("$E$3:$AN$3"))
    > I need to add another criterium "Dog". How do I modify?
    > Thanks
    > Rob




  3. #3
    JE McGimpsey
    Guest

    Re: Help with Sumproduct with Indirect

    The only advantage I can see to INDIRECT is that it allows one to insert
    columns/rows without the formula automatically adjusting.

    I probably could come up with a scenario where that made sense.

    In article <[email protected]>,
    "Arvi Laanemets" <[email protected]> wrote:

    > Why do you use INDIRECT here?


  4. #4
    Rob
    Guest

    Re: Help with Sumproduct with Indirect

    Thanks!

    "JE McGimpsey" wrote:

    > One way:
    >
    > =SUMPRODUCT(--($E$1:$AN$1<>"Cat"),--($E$1:$AN$1<>"Dog"),$E$3:$AN$3))
    >
    > convert ranges to INDIRECTs if necessary.
    >
    > In article <[email protected]>,
    > Rob <[email protected]> wrote:
    >
    > > I am currently using the following formula...
    > > =SUMPRODUCT((INDIRECT("$E$1:$AN$1")<>"Cat")*INDIRECT("$E$3:$AN$3"))
    > > I need to add another criterium "Dog". How do I modify?

    >


  5. #5
    Arvi Laanemets
    Guest

    Re: Help with Sumproduct with Indirect

    Hi


    "JE McGimpsey" <[email protected]> wrote in message
    news:[email protected]...
    > The only advantage I can see to INDIRECT is that it allows one to insert
    > columns/rows without the formula automatically adjusting.


    Then I would use dynamic named ranges instead.


    Arvi Laanemets



  6. #6
    JE McGimpsey
    Guest

    Re: Help with Sumproduct with Indirect

    In article <#[email protected]>,
    "Arvi Laanemets" <[email protected]> wrote:

    > Then I would use dynamic named ranges instead.


    I might, too, but at the same time, it would introduce a layer of
    obfuscation that using INDIRECT() doesn't.

  7. #7
    Rob
    Guest

    Help with Sumproduct with Indirect

    Hi
    I am currently using the following formula...
    =SUMPRODUCT((INDIRECT("$E$1:$AN$1")<>"Cat")*INDIRECT("$E$3:$AN$3"))
    I need to add another criterium "Dog". How do I modify?
    Thanks
    Rob

  8. #8
    JE McGimpsey
    Guest

    Re: Help with Sumproduct with Indirect

    One way:

    =SUMPRODUCT(--($E$1:$AN$1<>"Cat"),--($E$1:$AN$1<>"Dog"),$E$3:$AN$3))

    convert ranges to INDIRECTs if necessary.

    In article <[email protected]>,
    Rob <[email protected]> wrote:

    > I am currently using the following formula...
    > =SUMPRODUCT((INDIRECT("$E$1:$AN$1")<>"Cat")*INDIRECT("$E$3:$AN$3"))
    > I need to add another criterium "Dog". How do I modify?


  9. #9
    Arvi Laanemets
    Guest

    Re: Help with Sumproduct with Indirect

    Hi

    Why do you use INDIRECT here? Simply
    =SUMPRODUCT(($E$1:$AN$1<>"Cat")*($E$3:$AN$3))
    will work in same way. Even better will be
    =SUMPRODUCT(--($E$1:$AN$1<>"Cat"),($E$3:$AN$3))

    About your question - for which range will the additional criterium apply?
    When you want to sum all values in row 3 with "Dog" or "Cat" in row 1, then
    =SUMPRODUCT(--($E$1:$AN$1<>"Dog"),($E$3:$AN$3))+SUMPRODUCT(--($E$1:$AN$1<>"C
    at"),($E$3:$AN$3))
    When you want to sum all values in row 3 with "Cat" in row 1 and "Dog" in
    row 2, then
    =SUMPRODUCT(--($E$1:$AN$1<>"Cat"),--($E$2:$AN$2<>"Dog"),($E$3:$AN$3))


    Arvi Laanemets


    "Rob" <[email protected]> wrote in message
    news:[email protected]...
    > Hi
    > I am currently using the following formula...
    > =SUMPRODUCT((INDIRECT("$E$1:$AN$1")<>"Cat")*INDIRECT("$E$3:$AN$3"))
    > I need to add another criterium "Dog". How do I modify?
    > Thanks
    > Rob




  10. #10
    JE McGimpsey
    Guest

    Re: Help with Sumproduct with Indirect

    The only advantage I can see to INDIRECT is that it allows one to insert
    columns/rows without the formula automatically adjusting.

    I probably could come up with a scenario where that made sense.

    In article <[email protected]>,
    "Arvi Laanemets" <[email protected]> wrote:

    > Why do you use INDIRECT here?


  11. #11
    Rob
    Guest

    Re: Help with Sumproduct with Indirect

    Thanks!

    "JE McGimpsey" wrote:

    > One way:
    >
    > =SUMPRODUCT(--($E$1:$AN$1<>"Cat"),--($E$1:$AN$1<>"Dog"),$E$3:$AN$3))
    >
    > convert ranges to INDIRECTs if necessary.
    >
    > In article <[email protected]>,
    > Rob <[email protected]> wrote:
    >
    > > I am currently using the following formula...
    > > =SUMPRODUCT((INDIRECT("$E$1:$AN$1")<>"Cat")*INDIRECT("$E$3:$AN$3"))
    > > I need to add another criterium "Dog". How do I modify?

    >


  12. #12
    Arvi Laanemets
    Guest

    Re: Help with Sumproduct with Indirect

    Hi


    "JE McGimpsey" <[email protected]> wrote in message
    news:[email protected]...
    > The only advantage I can see to INDIRECT is that it allows one to insert
    > columns/rows without the formula automatically adjusting.


    Then I would use dynamic named ranges instead.


    Arvi Laanemets



  13. #13
    JE McGimpsey
    Guest

    Re: Help with Sumproduct with Indirect

    In article <#[email protected]>,
    "Arvi Laanemets" <[email protected]> wrote:

    > Then I would use dynamic named ranges instead.


    I might, too, but at the same time, it would introduce a layer of
    obfuscation that using INDIRECT() doesn't.

+ 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