+ Reply to Thread
Results 1 to 7 of 7

SUMIFS Multiple Columns

  1. #1
    Registered User
    Join Date
    07-22-2016
    Location
    UK
    MS-Off Ver
    2007
    Posts
    57

    SUMIFS Multiple Columns

    Hi I have the following formula which works fine for financials in AL...

    =SUMIFS(AL:AL, $Q:$Q, "Y")

    I have financials in AL and these will continually be added to.

    I also have financials in AM and AN. Is it possible to add up all values in columns AL, AM and AN where Q = Y?

    Or will I have to run the calculation 3 separate times and add up the result at the end?

  2. #2
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: SUMIFS Multiple Columns

    Please Login or Register  to view this content.
    If someone has helped you then please add to their Reputation

  3. #3
    Registered User
    Join Date
    07-22-2016
    Location
    UK
    MS-Off Ver
    2007
    Posts
    57

    Re: SUMIFS Multiple Columns

    Doesnt work at all if I use SUMIF

    If I use SUMIFS I get a #VALUE! error

  4. #4
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: SUMIFS Multiple Columns

    Hello
    I don't believe you can use a multi column sum range with SUMIF or SUMIFS. You will probably need to use SUMPRODUCT but this won't work when referencing entire columns as you're doing. It's not very efficient to do that. If you were to create dynamic ranges you could use something like.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Here 'Criteria' and 'Data' would be dynamic named ranges.

    DBY

  5. #5
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: SUMIFS Multiple Columns

    Quote Originally Posted by DBY View Post
    Hello
    I don't believe you can use a multi column sum range with SUMIF or SUMIFS. You will probably need to use SUMPRODUCT but this won't work when referencing entire columns as you're doing. It's not very efficient to do that. If you were to create dynamic ranges you could use something like.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Here 'Criteria' and 'Data' would be dynamic named ranges.

    DBY
    You should be able to, attached is a working example. I think my original formula had the criteria and ranges wrong way round.

    Should be:
    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: SUMIFS Multiple Columns

    @pjwhitfield

    Have you manually checked your results?

    Although it does not error, SUMIF cannot operate correctly with a sum_range of a different dimension to that of the range. In such cases, it reduces the larger of the two such that it is of an equal size to the smaller.

    Hence, your construction:

    =SUMIF(D:D,"a",A:C)

    is equivalent to:

    =SUMIF(D:D,"a",A:A)

    i.e. is not taking into consideration columns B and C at all for the sum, which can easily be manually verified.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  7. #7
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: SUMIFS Multiple Columns

    @pj

    I have looked at your example and as XOR confirms it's only summing column A not A:D the answer with Sumproduct is 3657 not 3227 from the Sumif, which is the total of column A. It's deceptive because the two figures are relatively close in the 3000's range.

    DBY

+ 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 or statement over multiple columns
    By sjennings007 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-07-2015, 11:05 PM
  2. [SOLVED] SUMIFS/AVERAGEIFS across multiple columns
    By keith740 in forum Excel General
    Replies: 30
    Last Post: 06-25-2015, 01:25 PM
  3. SUMIFs across multiple columns (SUMPRODUCT?)
    By source in forum Excel Formulas & Functions
    Replies: 22
    Last Post: 01-26-2015, 02:39 PM
  4. 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
  5. Multiple Criteria Sumifs in Multiple Columns
    By arowberry in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-04-2014, 06:06 PM
  6. Sumifs with multiple criteria in multiple columns
    By Ganesh7299 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-30-2013, 01:30 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