sumif based on vlookup array

[Brian Winkler]

I'd like to nest a VLOOKUP function returning an array within a SUMIF
function but receive a unspecific error message.

I'd like to take a column of id values, translate those values into an
array of group id's using VLOOKUP, and then use the result as the range
argument in the SUMIF formula (first argument). The criteria (second
argument) in the SUMIF formula would be a value found in the array of
group ids.

Can you recommend a way to nest an array of VLOOKUP results into the
range argument of SUMIF?

thanks

 

[Domenic]

Can you provide a sample along with your expected results?

Brian Winkler

 

[Brian Winkler]

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:B16).
But this doesn't work because SUMIF needs a range in the first
argument. I'd appreciate your suggestions.
thanks
Brian

 

[duane]

your data table in a2:b16 and your zip/state table in a20:b24

states listed in a27:a29

in cells b27:b29

=SUMPRODUCT(($A$2:$A$16>=MIN(IF($B$20:$B$24=$A27,$A$20:$A$24)))*($A$2:$A$16<=MAX(IF($B$20:$B$24=$A27,$A$20:$A$24)))*($B$2:$B$16))

an array formula so control+shift+ente

 

[Brian Winkler]

Duane:

Thank you. This is an excellent suggestion. How would you write it if
the zips were alphanumeric?


Brian

 

[Domenic]

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!

Brian Winkler

 

[Brian Winkler]

Domenic,
Thanks. This was really helpful.

Brian