+ Reply to Thread
Results 1 to 10 of 10

SUMIF VLOOKUP Results From Multiple Columns

  1. #1
    Forum Contributor PY_'s Avatar
    Join Date
    09-23-2008
    Location
    Houston
    MS-Off Ver
    Office 2016
    Posts
    289

    Angry SUMIF VLOOKUP Results From Multiple Columns

    Hi All,
    I have a complex SUMIF that I am having the hardest time with. I have worked my way around it by coming up with a crazy worksheet using all kinds of tricks but i was hoping i can calculate my number with a much simpler formula instead. I have attached a sample file with my issue layed out.

    ColumnA = Departments
    ColumnB = SUMIF results

    ColumnD = Username
    ColumnE = User's Number I want to SUM

    ColumnsG:K = VLOOKUP table containing the Departments users are a part of (in multiple columns because a single user can be in multiple departments)
    Attached Files Attached Files
    Last edited by PY_; 09-30-2016 at 11:47 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: SUMIF VLOOKUP Results From Multiple Columns

    Try:

    =SUMPRODUCT(SUMIF($A$3:$A$7,INDEX($H$3:$K$64,MATCH(D3,$G$3:$G$64,0),0),$B$3:$B$7))

    copied down.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

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

    Re: SUMIF VLOOKUP Results From Multiple Columns

    Hi,

    So which are the results in this file that you want us to generate?

    Regards
    Click * below if this answer helped

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

  4. #4
    Forum Contributor PY_'s Avatar
    Join Date
    09-23-2008
    Location
    Houston
    MS-Off Ver
    Office 2016
    Posts
    289

    Re: SUMIF VLOOKUP Results From Multiple Columns

    That formula you entered does not touch the number i am trying to sum (9,523)

    The result numbers: ColumnB = SUMIF results

    One thing i did not mention is that the user and user number list (Column C & D) can be MANY rows long

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

    Re: SUMIF VLOOKUP Results From Multiple Columns

    Quote Originally Posted by PY_ View Post
    The result numbers: ColumnB = SUMIF results
    So you've already generated these yourself? In that case which are the expected results in this file?

    Regards

  6. #6
    Forum Contributor PY_'s Avatar
    Join Date
    09-23-2008
    Location
    Houston
    MS-Off Ver
    Office 2016
    Posts
    289

    Re: SUMIF VLOOKUP Results From Multiple Columns

    Those results were manually calculated using a calculator. I need to replace those values in ColumnB with a formula to result in the same values.

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: SUMIF VLOOKUP Results From Multiple Columns

    Quote Originally Posted by PY_ View Post
    That formula you entered does not touch the number i am trying to sum (9,523)

    The result numbers: ColumnB = SUMIF results

    One thing i did not mention is that the user and user number list (Column C & D) can be MANY rows long
    My formula looks at your lookup table for the name of the user and finds all the departments they are in, then the formula sums of counts from column B for those departments.... i guess that isn't what you were looking for?

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

    Re: SUMIF VLOOKUP Results From Multiple Columns

    Perhaps there's a simpler way of achieving these results (and certainly if you were to rearrange your set-up), but perhaps, array formula** in B3:

    =SUM(SUMIF(D$3:D$7,INDEX(G:G,N(IF(1,MODE.MULT(IF(H$3:K$64=A3,ROW(H$3:K$64)),ROW(H$3:K$64))))),E$3:E$7))

    and copy down.

    Regards


    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

  9. #9
    Forum Contributor PY_'s Avatar
    Join Date
    09-23-2008
    Location
    Houston
    MS-Off Ver
    Office 2016
    Posts
    289

    Re: SUMIF VLOOKUP Results From Multiple Columns

    XOR LX, that last solution works perfectly!

    I wish i could rearrange the setup but unfortunately due to the way i am gathering the two tables, i can not. Now to see if i can apply your formula to my actual data. Then to try to wrap my head around understanding exactly what your doing in this formula.

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

    Re: SUMIF VLOOKUP Results From Multiple Columns

    You're welcome!

+ 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. results Vlookup / sumif formula needed
    By jono121ukk in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-14-2016, 05:13 PM
  2. Replies: 1
    Last Post: 12-14-2015, 09:56 AM
  3. Help using vlookup to return multiple results for one vlookup value
    By Akmon in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-27-2015, 06:00 AM
  4. [SOLVED] using Vlookup with multiple results and averaging the results.
    By xatomicx in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-12-2015, 10:19 AM
  5. VLOOKUP - Returning results x No. of Columns to Left
    By watchouse in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-08-2012, 07:00 AM
  6. Creating a Table from Multiple Columns with Multiple results
    By ChemistB in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-15-2010, 03:27 PM
  7. Matching multiple columns, summing multiple results
    By DamienC in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-09-2008, 09:36 AM

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