Home |
Search |
Today's Posts |
#1
|
|||
|
|||
sumif based on vlookup array
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 ------------------------------------------------------------------------ Brian Winkler's Profile: http://www.excelforum.com/member.php...o&userid=27867 View this thread: http://www.excelforum.com/showthread...hreadid=473736 |
#2
|
|||
|
|||
Can you provide a sample along with your expected results?
In article , Brian Winkler 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 |
#3
|
|||
|
|||
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 -- Brian Winkler ------------------------------------------------------------------------ Brian Winkler's Profile: http://www.excelforum.com/member.php...o&userid=27867 View this thread: http://www.excelforum.com/showthread...hreadid=473736 |
#4
|
|||
|
|||
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 -- duane ------------------------------------------------------------------------ duane's Profile: http://www.excelforum.com/member.php...o&userid=11624 View this thread: http://www.excelforum.com/showthread...hreadid=473736 |
#5
|
|||
|
|||
Duane: Thank you. This is an excellent suggestion. How would you write it if the zips were alphanumeric? Brian -- Brian Winkler ------------------------------------------------------------------------ Brian Winkler's Profile: http://www.excelforum.com/member.php...o&userid=27867 View this thread: http://www.excelforum.com/showthread...hreadid=473736 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
sumif based on vlookup array
Domenic, Thanks. This was really helpful. Brian -- Brian Winkler ------------------------------------------------------------------------ Brian Winkler's Profile: http://www.excelforum.com/member.php...o&userid=27867 View this thread: http://www.excelforum.com/showthread...hreadid=473736 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using single cell reference as table array argument in Vlookup | Excel Worksheet Functions | |||
format cell based on results of vlookup function | Excel Worksheet Functions | |||
Array Function with VLOOKUP | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
SUM(IF( Array to avoid #NUM! values | Excel Worksheet Functions |