Nesting Sumproduct and Sumifs not working
Results 1 to 4 of 4

Thread: Nesting Sumproduct and Sumifs not working
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2011
    Posts
    1,143
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Nesting Sumproduct and Sumifs not working

    Hi

    I am trying to change this formula to use Sumifs to make it faster (trying to use better formulas to speed up my spreadsheet) but i cant seem to get it to work

    I am trying to do a multiple or with sumproduct

    my current formula

    =SUMPRODUCT(--(A2:A7=F7),--ISNUMBER(MATCH(B2:B7,Condition_List,0))),(D2:D7))

    trying to change to

    =SUMPRODUCT(--(A2:A7=F7),Sumifs(B2:B7,Condition_List),(D2:D7))

    what am i doing wrong

  2. #2
    Board Regular
    Join Date
    Feb 2005
    Location
    Melbourne (Australia)
    Posts
    578
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Nesting Sumproduct and Sumifs not working

    SUMIFS() is returning a single value.

    --ISNUMBER() is returning an array of 6 values.....

    The two formulas are doing completely different things. SUMIFS() is already adding up the cells, before applying the second criteria. A = F criteria
    "I'm almost sure I'm not mad..." - Stoppard

  3. #3
    Board Regular
    Join Date
    Nov 2011
    Posts
    1,143
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Nesting Sumproduct and Sumifs not working

    Im sure i read i can encorporate Sumifs to make it quicker

    i may be wrong though

  4. #4
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,554
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Nesting Sumproduct and Sumifs not working

    You can do that with a sumproduct sumifs:

    =SUMPRODUCT(SUMIFS(D2:D7,A2:A7,F7,B2:B7,Condition_List))
    Looking for opportunities

Some videos you may like

User Tag List

Tags for this Thread

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
  •