+ Reply to Thread
Results 1 to 6 of 6

SUMIFS of multiple columns in a range

  1. #1
    Registered User
    Join Date
    04-10-2019
    Location
    Phoenix, AZ
    MS-Off Ver
    2010
    Posts
    3

    SUMIFS of multiple columns in a range

    Hello, it's my first time posting so I hope this is alright. I'm trying to get the sum from a table, of all the numbers in multiple columns (separated by columns with unnecessary information), also based on an IF check for a person's name in a column on the left. I'll post my formula below. It isn't working, could anyone help me understand why? Or suggest any better alternative? The range will become larger every week, so I want a formula that's not too long.

    =SUMIFS('2018 WK1-26'!$G:$DG,'2018 WK1-26'!$B:$B,Summary!$B16,'2018 WK1-26'!$D$7:$DG$7,"Amt")

    What I was trying to do, was have it do a check on the names in one column, and then do a second check for the word "Amt" in the row with the column headers, and have it sum all of the cells under each Amt column for each name. I hope I'm explaining this adequately.
    Last edited by Ruuroga; 04-10-2019 at 03:04 PM.

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: SUMIFS of multiple columns in a range

    =SUM('2018 WK1-26'!$G:$DG*('2018 WK1-26'!$B:$B=Summary!$B16)*('2018 WK1-26'!$D$7:$DG$7="Amt")), CSE
    PS referencing the whole columns is not quite effective

  3. #3
    Registered User
    Join Date
    04-10-2019
    Location
    Phoenix, AZ
    MS-Off Ver
    2010
    Posts
    3

    Re: SUMIFS of multiple columns in a range

    Ok, so I tried the formula you gave me, but it's returning 0 for the total. I'm relatively new to Excel so could you explain what the * marks are in this formula? It's not multiplying anything is it?

    Also, what would be a better option than referencing the whole column? is that just for the checks, or for the sum range as well?
    Last edited by Ruuroga; 04-10-2019 at 04:43 PM.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,256

    Re: SUMIFS of multiple columns in a range

    Hello Ruuroga and Welcome to Excel Forum.
    In the attached sample file the formula used is: =SUMPRODUCT(('2018 Wk 1-26'!B2:B8=B2)*('2018 Wk 1-26'!C1:H1="Amt")*('2018 Wk 1-26'!C2:H8))
    If that were translated to your file it might read: =SUMPRODUCT(('2018 Wk 1-26'!B:B=B16)*('2018 Wk 1-26'!D7:DG7="Amt")*('2018 Wk 1-26'!D:DG))
    If that doesn't work for your file, please upload a small desensitized sample by clicking on the GO ADVANCED button below the Quick Reply window and then scrolling down to Manage Attachments to open the upload window.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    04-10-2019
    Location
    Phoenix, AZ
    MS-Off Ver
    2010
    Posts
    3

    Re: SUMIFS of multiple columns in a range

    Thank you so much for the reply!
    I got busy with other projects at work so I haven't been able to get back to this one.

    Your formula is written so clearly! Thank you!! I can actually understand where it's referencing. I will try it out and see if it works!

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,256

    Re: SUMIFS of multiple columns in a range

    You're Welcome and thank you for the generous feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. SUMIFS with sum value in multiple columns
    By XLFriend in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-06-2018, 12:50 PM
  2. SUMIFS Multiple Columns
    By nathanexcelhelp in forum Excel General
    Replies: 6
    Last Post: 09-22-2016, 06:46 AM
  3. [SOLVED] Using sumifs where sum range's columns are non contiguous?
    By GIS2013 in forum Excel General
    Replies: 16
    Last Post: 04-18-2016, 09:17 AM
  4. SUMIFS with two columns in range
    By MrPablus in forum Excel General
    Replies: 3
    Last Post: 04-04-2016, 01:00 PM
  5. Replies: 3
    Last Post: 12-09-2015, 03:10 AM
  6. Can I use sumifs on a range with multiple columns?
    By JasperD in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-13-2014, 03:11 AM
  7. SUMIFS with multiple sum range columns
    By bvmk in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-11-2013, 03:39 PM

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