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?

In article
<[email protected]>,
Brian Winkler
<[email protected]> wrote:

> 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

[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+enter

[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!

In article
<[email protected]>,
Brian Winkler
<[email protected]> 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

[Brian Winkler]

Domenic,
Thanks. This was really helpful.

Brian