Clive,

You need to bring Job Count info next to your weights table with
VLOOKUP. So in D2 of Sheet1 (weights) use:

=VLOOKUP(B2,Sheet2!A:B,2,0)

Once you have the expanded table you can list all your area codes in a
separate area, say Sheet3!A2:A21. Then, next to Area1, in Sheet3!B2
use:

=SUMPRODUCT((Sheet1!$A$2:$A$101=A2)*Sheet1!$C$2:$C $101*Sheet1!$D$2:$D
$101)/100

Copy down

HTH
Kostis Vezerides

On Sep 2, 10:21*am, quetzalc0atl wrote:
Dear Learned Excel Gurus,

I'm currently working on a calculation which I believe to be quite
complicated. *I was wondering if anyone could please help me simplify
or solve this problem.

I'm currently trying to re-calculate job counts from one smaller
geographic area (LSOAs) to another larger one (Neighbourhoods). *Many
of the LSOAs crosscut the larger neighbourhood areas and have been
given weightings of where they fall. *Some neighbourhoods may contain
one or two LSOAs and some can contain as many as 10 LSOAs. *There are
43 neighbourhoods and 160 LSOAs.

E.g.

Neighbourhood * LSOA * *Weighting
Area1 * LSOA1 * 40
Area1 * LSOA2 * 20
Area1 * LSOA3 * 100
Area1 * LSOA4 * 100
Area2 * LSOA2 * 20
Area2 * LSOA5 * 95
Area2 * LSOA6 * 75
Area2 * LSOA7 * 60
Area2 * LSOA8 * 99
Area2 * LSOA9 * 80
Area3 * LSOA2 * 60
Area3 * LSOA1 * 20
Area3 * LSOA10 *35
etc

Given the table below I need to calculate the job count for each of
the 43 Neighbourhood areas using the weightings lookup table above
which is on another tab.

LSOA * *Job Count
LSOA1 * 25
LSOA2 * 20
LSOA3 * 5
LSOA4 * 35
LSOA5 * 15
LSOA6 * 30
LSOA7 * 15
LSOA8 * 35
LSOA9 * 25
LSOA10 *45
etc

This calculation is something I wish to distribute to other
departments - most of which do not use MS Access. *I've been racking
my brains for a good few days now on how to do this in Excel but I
have drawn a complete blank. *I think I need some fresh perspective on
this - any help would be greatly appreciated.

Thanks,

Clive