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

[HeyInKy]

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:

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

[HeyInKy]

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

[FlameRetired]

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.

[HeyInKy]

Sorry! Trying this again...

Sample wkbk 1.xlsx

[FlameRetired]

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?

[HeyInKy]

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.

[joris moerings]

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.

[HeyInKy]

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.

[HeyInKy]

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!

[joris moerings]

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