Assumptions:

A2:B16 contains your source table

D2:E6 contains your lookup table

G2:G4 contains your list of states

Formula:

H2, copied down:

=SUMPRODUCT(SUMIF($A$2:$A$16,LEFT($D$2:$D$6,LEN($D $2:$D$6)*($E$2:$E$6=G2)
),$B$2:$B$16))

or

=SUM(SUMIF($A$2:$A$16,IF($E$2:$E$6=G2,$D$2:$D$6),$ B$2:$B$16))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article
,
Brian Winkler
wrote:

Yes thanks. For example, let's say there's a data table of zip codes,
each of which appears multiple times with a varying amount for some
observation:
zip amt
10011 1
10011 2
10011 3
10012 4
10012 5
10012 6
18101 7
18101 8
18101 9
18102 10
18102 11
18102 12
94105 13
94105 14
94105 15

Now we also have a lookup table indicating the state for each zip
code:
zip state
10011 NY
10012 NY
18101 PA
18102 PA
94105 CA

I need a single formula referencing these two tables which can pull the
total for any given state:
state amt
NY 21
PA 57
CA 42

I can't add a new column to the data table with the state names (in
that case this would just need SUMIF). My initial thought was to use
VLOOKUP to translate the zip codes into the state names and use the
resulting array in SUMIF. Something like:
=SUMIF(VLOOKUP(DataTable!A1:A16,LookupTable!A1:B6, 2,FALSE),"NY",DataTable!B1:B
16).
But this doesn't work because SUMIF needs a range in the first
argument. I'd appreciate your suggestions.
thanks
Brian