SUMIF/SUMPRODUCT across multiple tables question
Results 1 to 9 of 9

Thread: SUMIF/SUMPRODUCT across multiple tables question
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default SUMIF/SUMPRODUCT across multiple tables question

    Hi,

    I am close to my answer but, despite searching, keep missing the mark. I think it is an easy fix and appreciate the help. The gist is: I can't figure out the syntax for SUMPRODUCT to work across multiple tables if some of that data contains text. I would prefer to keep the answer as simple and readable as possible. I likely just need to rewrite the SUMPRODUCT to include those "--" things.

    In the screenshot from this link -- https://i.imgur.com/TbfCmJN.png -- you can see there are multiple tables for each month. Sometimes the data will have information for column 2, 3, etc, and sometimes not. But I need to retain the ability to sort it independently within each month.


    • =SUMPRODUCT((C3:I45=P3)*F3:F45) works across multiple tables but when it encounters the text in F33, it messes up with a #VALUE ! error. How do I use those "--" things or IFERROR to ignore it? I am very close...



    • =SUMIF(C3:H58,P8,F3:F58) will ignore the F33 error, but it only lets me search/input text from column C. Is there a way to expand the SUMIF to look for the "P8" across more than one column? SUMPRODUCT formula finds "wages" and gives me the total from but SUMIF does not.



    It's something like =SUMPRODUCT(--(C3:I45=P3),F3:F45) right, or throwing the -- before F3:F45?


    Thanks!

  2. #2
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    301
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIF/SUMPRODUCT across multiple tables question

    Hi, your this formula should work =SUMPRODUCT(--(C3:C45=P3),F3:F45) with changing I45 to C45.
    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIF/SUMPRODUCT across multiple tables question

    Hi, thanks for the response Aryatect.

    Unfortunately, changing I45 to C45 reduces the range to search for a user's input that I need. For example -- you can refer to the screenshot -- if someone were to type "car", they would get the correct amount of $2107.28 because of the SUMPRODUCT matching H19:H21 with F19:F21 in table one. I need it to accomplish this but include multiple tables. the text in column headers kills it thought.

    SUMIF ignores the text but does not allow excel to search for the input beyond one column. SUMPRODUCT lets me search in an expanded range but messes up when it encounters text. Is it simply a matter of moving the "--" to somewhere before the bold part of =SUMPRODUCT((C3:I45=P3)*F3:F45) since that is the range which will have the errors?

    I suppose I could stack sumifs somehow but that is not ideal... Sorry, I am trying to keep this clear but perhaps failing.

  4. #4
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,228
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: SUMIF/SUMPRODUCT across multiple tables question

    See if this example with a small data sample helps

    A
    B
    C
    D
    E
    F
    G
    H
    1
    Info1
    Info2
    Info3
    Amount
    Info4
    INPUT
    Result
    2
    ado
    ado
    10
    ado
    46
    3
    12
    ado
    4
    xxx
    14
    5
    6
    Info1
    Info2
    Info3
    NOOO
    7
    ado
    10
    8
    zzz
    12
    9
    ado
    yyy
    14


    Criteria in G2

    Formula in H2
    =SUMPRODUCT(--((A2:A9=G2)+(B2:B9=G2)+(C2:C9=G2)+(E2:E9=G2)>0),D2:D9)

    M.

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,687
    Post Thanks / Like
    Mentioned
    445 Post(s)
    Tagged
    45 Thread(s)

    Default Re: SUMIF/SUMPRODUCT across multiple tables question

    The arrays in sumproduct need to be the same size, try
    =SUMPRODUCT(SIGN((C3:C45=P3)+(D3:D45=P3)+(E3:E45=P3)+(G3:G45=P3)),F3:F45)
    just expand it to suit
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    New Member
    Join Date
    Jul 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIF/SUMPRODUCT across multiple tables question

    Hi Marcelo,

    That works, thanks. I thought it was simply a syntactical matter of adding those "--" before the range, but I can work with this for now.


    =SUMPRODUCT(--((A2:A9=G2)+(B2:B9=G2)+(C2:C9=G2)+(E2:E9=G2)>0),D2:D9)

    I guess I don't immediately grasp the logic of it, maybe my brain is just fuzzy from messing with it for too long. The "--" applies to D2:D9 and assigns a negative / 0 / ignore value to anything with text, right? And I don't see why the >0 is necessary or what it protects against? Sorry.

    Thanks for the help either way!

  7. #7
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,228
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: SUMIF/SUMPRODUCT across multiple tables question

    >0 is necessary because in my example row2 has two instances of "ado". It avoids double counting of this row.

    See the formula suggested by Fluff - it also works and doesn't require >0

    M.
    Last edited by Marcelo Branco; Jul 17th, 2019 at 02:11 PM.

  8. #8
    New Member
    Join Date
    Jul 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIF/SUMPRODUCT across multiple tables question

    Quote Originally Posted by Marcelo Branco View Post
    >0 is necessary because in my example row2 has two instances of "ado". It avoids double counting of this row.

    See the formula suggested by Fluff - it also works and doesn't require >0

    M.
    Ahhh,

    Thanks to everyone for their help.

  9. #9
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,228
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: SUMIF/SUMPRODUCT across multiple tables question

    You are welcome. Thanks for the feedback.

    M.

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
  •