+ Reply to Thread
Results 1 to 10 of 10

Using SUMPRODUCT(COUNTIFS(...)) to add values based on a lookup table

  1. #1
    Forum Contributor
    Join Date
    04-14-2013
    Location
    Ky
    MS-Off Ver
    Excel 2013
    Posts
    344

    Using SUMPRODUCT(COUNTIFS(...)) to add values based on a lookup table

    On sheet 1, I have a table (t1) with a header row and then 120 different variables in column A, named range "Var_Location", followed by numerical data in the adjoining columns, B:AB (Table is A1:AB121).

    On sheet 2, I have a reference table (t2), listing those 120 variables in column A, and then 3 columns of various lookups. In col. B, each variable is assigned one of 10 regions (named range region_lookup), col. C each has a sub-region (50 total, named subreg_lookup), and in D each variable has one of 60 contact names (range: name_lookup).

    I then have three tables (t3, t4 and t5) on sheets 3, 4 and 5, with t3 listing the 10 regions listed in column B on t2, t3 the 50 sub-regions, and t4 the 60 contacts.

    I need column B in tables 3-5 to add the corresponding values in column B for t1, looking up the reference in t2.

    So for the first region on sheet 3, cell A2 (it has the same headers as t1), my formula is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    This formula is copied down for each of the 10 regions, and across the columns thru AB... the totals for the the 10 regions in column B (and all other columns) match the totals for column B on t1, but manually counting the individual region totals, the numbers are not correct.

    Here's what I've found. My formula is fine and works correctly as long as t1 remains in it's original state - but when I sort it by any of the columns (and my Var_Location variables get out of order), the formulas in t3-6 return incorrect totals.

    Sorry I can't post a sample - too much sanitizing!

    EDIT: Attached a sample... look at sheets 3 & 4, then sort the table on sheet 1, and look again at sheets 3 & 4... the values will be off.

    Attachment 491237
    Last edited by HeyInKy; 11-28-2016 at 03:40 PM.

  2. #2
    Forum Contributor
    Join Date
    04-14-2013
    Location
    Ky
    MS-Off Ver
    Excel 2013
    Posts
    344

    Re: Using SUMPRODUCT(COUNTIFS(...)) to add values based on a lookup table

    UPDATE: attached a sample workbook if anyone cares to play with. Thanks!

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,614

    Re: Using SUMPRODUCT(COUNTIFS(...)) to add values based on a lookup table

    I got an "Invalid attachment" pop-up.

    If you are unfamiliar with how to upload a file to the forum:

    To attach a file to your post,
    • click “Go Advanced” (next to Post Quick Reply – bottom right),
    • scroll down until you see “Manage Attachments”, click that,
    • click “Browse”.
    • select your file(s)
    • click “Upload”
    • click “Close window”
    • click “Submit Reply”
    The file name will appear at the bottom of your reply.
    Dave

  4. #4
    Forum Contributor
    Join Date
    04-14-2013
    Location
    Ky
    MS-Off Ver
    Excel 2013
    Posts
    344

    Re: Using SUMPRODUCT(COUNTIFS(...)) to add values based on a lookup table

    Sorry! Trying this again...

    Sample wkbk 1.xlsx

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,614

    Re: Using SUMPRODUCT(COUNTIFS(...)) to add values based on a lookup table

    I then have three tables (t3, t4 and t5) on sheets 3, 4 and 5
    HeyInKy,

    I find no sheet 5 or table 5 in the upload. An oversight?

  6. #6
    Forum Contributor
    Join Date
    04-14-2013
    Location
    Ky
    MS-Off Ver
    Excel 2013
    Posts
    344

    Re: Using SUMPRODUCT(COUNTIFS(...)) to add values based on a lookup table

    No it was just a quick sample based on my original sheet, but obviously not it. Try this - copy the table on sheet 3 or 4 as it is and paste values and formatting beginning in column E (leaving col. D blank so it doesn't become a part of the existing table. This will give you the static (correct answers). Then go to sheet 1 and sort by either col. B or C, descending or ascending - anything to change it up - and then compare the new values in the tables on sheets 3 and 4 after sorting to the static answers.

  7. #7
    Forum Contributor
    Join Date
    06-30-2015
    Location
    Netherlands
    MS-Off Ver
    2013 / 2016
    Posts
    143

    Re: Using SUMPRODUCT(COUNTIFS(...)) to add values based on a lookup table

    Hi,

    With using the COUNTIF the criteria doesn't change the order because the order of the original table isn't changed. Change your COUNTIF part to LOOKUP and it will do the trick.
    Attached Files Attached Files
    HtH,

    Joris

    --------------------------------------------------------------------------------
    If you found my answer helpful, please hit the Like or Thank button.

    Please follow the forum Rules and Guidelines and use Code tags around your VBA code.

    Remember: test VBA code always on a copy of your file because usually you can't use undo

  8. #8
    Forum Contributor
    Join Date
    04-14-2013
    Location
    Ky
    MS-Off Ver
    Excel 2013
    Posts
    344

    Re: Using SUMPRODUCT(COUNTIFS(...)) to add values based on a lookup table

    Thanks Joris... while I can clearly see this is working on my sample sheet, when I tried applying the logic on my main workbook, I'm getting an #N/A error. The only thing I can see you did differently is you added 2 columns to table4 on sheet 1 with VLOOKUPS for the region and sub-region, but I don't see where your formulas ever referred to or used those columns.

  9. #9
    Forum Contributor
    Join Date
    04-14-2013
    Location
    Ky
    MS-Off Ver
    Excel 2013
    Posts
    344

    Re: Using SUMPRODUCT(COUNTIFS(...)) to add values based on a lookup table

    UPDATE: on my original workbook, there was an extra blank row after my first table that was being counted in my table and causing the error.. deleting the row, and everything works Thank you!!

    One follow-up, which I'm going to research but will post here anyway, since my original workbook has columns through AB, it's a hassle manually changing the last reference in your formula to match the current formula, but dragging it across changes the column references appearing earlier in the formula - is there a way to make those absolute?

    Thanks again!

  10. #10
    Forum Contributor
    Join Date
    06-30-2015
    Location
    Netherlands
    MS-Off Ver
    2013 / 2016
    Posts
    143

    Re: Using SUMPRODUCT(COUNTIFS(...)) to add values based on a lookup table

    Hi,

    Glad i could help and thanks for the rep points.
    On your investigation of changing the formula to absolute.:

    eg: change the part of the formula from "table4[sales]" towards "table4[[sales]:[sales]]"
    that would create an absolute reference, this goes for all table column names

+ 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. Lookup and sum values based on table headers
    By enterthe in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 04-18-2016, 10:17 AM
  2. [SOLVED] Lookup and return table values based on tab name
    By RaydenUK in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 03-10-2015, 07:30 AM
  3. Replies: 0
    Last Post: 06-04-2014, 10:08 AM
  4. [SOLVED] I need COUNTIFS or SUMPRODUCT formula to count ONLY Non-Consecutive values
    By justinbelkin in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-19-2013, 03:24 PM
  5. Lookup values from a table based on two criteria
    By Alun in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-03-2012, 05:33 PM
  6. Table Lookup based on values in 5 cells
    By TalResha in forum Excel General
    Replies: 3
    Last Post: 05-18-2012, 10:50 AM
  7. SUM Values based off Lookup Table
    By carlyman in forum Excel General
    Replies: 14
    Last Post: 08-18-2011, 09:10 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