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:

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

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.

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?

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.

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