Results 1 to 5 of 5

Thread: GOOGLE SHEETS: SUMIF with multiple range columns and multiple sumrange columns?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Sep 2002
    Posts
    766
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default GOOGLE SHEETS: SUMIF with multiple range columns and multiple sumrange columns?

    I have a spreadsheet set out with 10 unique text entries in column A (row 2 through 11), and the same values in column C & E. In B, D, & F, I have a corresponding value that I am trying to sum. The easy solution is to put them all in one column, and use a basic sumif, but the spreadsheet needs to be laid out this way. How can I get it to work over multiple columns for both the range and sumrange? N2, N3, and so on is my criteria I'm matching it to. Here's where I'm at with the formula:

    =sumif((A$2:A$11,C$2:C$11,E$2:E$11),N2,(B$2:B$11,D$2:D$11,F$2:F$11))

    Anyone know what I'm doing wrong, or is this not possible with sumif?
    Last edited by slam; Jul 12th, 2019 at 10:57 AM.
    Excel 2010

  2. #2
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,642
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

    Default Re: SUMIF with multiple range columns and multiple sumrange columns?

    You could do this:

    =SUMPRODUCT(SUMIF(INDIRECT({"A$2:A$11","C$2:C$11","E$2:E$11"}),N2,INDIRECT({"B$2:B$11","D$2:D$11","F$2:F$11"})))

    or simply add 3 sumif formulas together.

  3. #3
    Board Regular
    Join Date
    Sep 2002
    Posts
    766
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIF with multiple range columns and multiple sumrange columns?

    Quote Originally Posted by steve the fish View Post
    You could do this:

    =SUMPRODUCT(SUMIF(INDIRECT({"A$2:A$11","C$2:C$11","E$2:E$11"}),N2,INDIRECT({"B$2:B$11","D$2:D$11","F$2:F$11"})))

    or simply add 3 sumif formulas together.
    Well, that does indeed work perfectly in Excel, and I assumed it would in googlesheets too, but that's not the case
    Excel 2010

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,715
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: SUMIF with multiple range columns and multiple sumrange columns?

    Well, that does indeed work perfectly in Excel, and I assumed it would in googlesheets too, but that's not the case
    Is this an Excel or a Google Sheets problem? Note that they are NOT the same thing, and do not necessarily work the same.

    If you post Google Sheets questions, be sure to mention that in the title and/or post, and it should be posted to the "General Excel Discussion & Other Questions" forum, and not the "Excel Questions" forum.
    I can move this thread for you, if this in indeed a Google Sheets question.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    Board Regular
    Join Date
    Sep 2002
    Posts
    766
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUMIF with multiple range columns and multiple sumrange columns?

    Quote Originally Posted by Joe4 View Post
    Is this an Excel or a Google Sheets problem? Note that they are NOT the same thing, and do not necessarily work the same.

    If you post Google Sheets questions, be sure to mention that in the title and/or post, and it should be posted to the "General Excel Discussion & Other Questions" forum, and not the "Excel Questions" forum.
    I can move this thread for you, if this in indeed a Google Sheets question.
    Yes, my apologies for not specifying. I have access to both, which is why I was able to see it worked in Excel, but not googlesheets. I wouldn't have expected something like this would behave differently in each.

    That said, I have found an alternative solution in googlesheets, by reworking the layout of data, so I don't need to pursue a solution any further. Thank you for your time.
    Excel 2010

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
  •