+ Reply to Thread
Results 1 to 6 of 6

Simplifying a SUMIF over multiple columns

  1. #1
    Registered User
    Join Date
    03-04-2016
    Location
    San Diego
    MS-Off Ver
    13
    Posts
    13

    Simplifying a SUMIF over multiple columns

    Hello,
    I am thinking there is a simpler way to write a sumif formula for a criteria occurring in multiple columns. Please see attached (at least, I believe it is attached).

    I am trying to total vendor amounts, but the same vendor can be listed in different columns, with an associated dollar amount in respective adjacent columns, as per the attached. I have a formula that works, but it is clumsy:
    =SUMIF(B$7:B$319,AB7,C$7:C$319)+SUMIF(D$7:D$319,AB7,E$7:E$319)+SUMIF(F$7:F$319,AB7,G$7:G$319)+SUMIF(H$7:H$319,AB7,I$7:I$319)+SUMIF(J$7:J$319,AB7,K$7:K$319)+SUMIF(L$7:L$319,AB7,M$7:M319)



    Tips on simplifying? Any help is appreciated.
    Thanks in advance, and apologies if this has been posted before.
    Attached Files Attached Files

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Simplifying a SUMIF over multiple columns

    I didn't download your file.

    Try it like this:

    =SUMIF(B$7:L$319,AB7,C$7:M$319)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,885

    Re: Simplifying a SUMIF over multiple columns

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

    Note! This is an array formula, it has to be entered with Ctrl + Shift + Enter.

    Edit: Too late! Go with Tony's non-array solution.
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  4. #4
    Registered User
    Join Date
    03-04-2016
    Location
    San Diego
    MS-Off Ver
    13
    Posts
    13

    Re: Simplifying a SUMIF over multiple columns

    Thank you for your responses. I tried Tony's solution and it worked, so I rolled with that. I did tinker around with Jacc's, but need to trouble shoot, as i wasn't totaling up correctly....my error, I'm sure.

    Thanks again.

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Simplifying a SUMIF over multiple columns

    You're welcome. Thanks for the feedback!

  6. #6
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,885

    Re: Simplifying a SUMIF over multiple columns

    Quote Originally Posted by HeadSkrach View Post
    Thank you for your responses. I tried Tony's solution and it worked, so I rolled with that. I did tinker around with Jacc's, but need to trouble shoot, as i wasn't totaling up correctly....my error, I'm sure.

    Thanks again.
    For info, the key is to use Ctrl + Shift + Enter, not just enter.
    Attached Files Attached Files

+ 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. [SOLVED] Conditional SUMIF based on multiple columns and multiple row variables
    By jaymaan74 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-15-2016, 11:05 AM
  2. Excel 2007 : Simplifying multiple IF codes
    By dip11 in forum Excel General
    Replies: 7
    Last Post: 06-10-2011, 09:37 AM
  3. Using SUMIF across multiple columns
    By BobTheRocker in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-26-2011, 01:03 PM
  4. SUMIF over multiple columns
    By SueJ in forum Excel General
    Replies: 2
    Last Post: 05-25-2010, 09:56 AM
  5. SUMIF Multiple columns
    By BW29 in forum Excel General
    Replies: 2
    Last Post: 06-04-2008, 12:18 PM
  6. [SOLVED] SUMIF with multiple criteria for multiple columns to sum a single
    By SavageMind in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-27-2005, 11:05 AM
  7. [SOLVED] Sumif over multiple columns
    By Josh O. in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-15-2005, 06:45 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