Home |
Search |
Today's Posts |
#1
|
|||
|
|||
lookup and return range of cells
I want to find the sum of a range of values in a table, but I want this range
to be set by a lookup function. My data table has numerous entries for each value I want to lookup (say there are 21 matches of value "a", and then I want to sum up what corresponds to all those "a" values.). Any help? Let me know if you need more info. Thanks! |
#2
|
|||
|
|||
=sumproduct(--(lookup_range="a"),value_range)
lookup_range MUST be the same size as value_range or this won't work. Thus it needs to be something like =sumproduct(--(A2:A200="a"),B2:B200) "ark" wrote: I want to find the sum of a range of values in a table, but I want this range to be set by a lookup function. My data table has numerous entries for each value I want to lookup (say there are 21 matches of value "a", and then I want to sum up what corresponds to all those "a" values.). Any help? Let me know if you need more info. Thanks! |
#3
|
|||
|
|||
fantastic, it works great. thanks.
"Duke Carey" wrote: =sumproduct(--(lookup_range="a"),value_range) lookup_range MUST be the same size as value_range or this won't work. Thus it needs to be something like =sumproduct(--(A2:A200="a"),B2:B200) "ark" wrote: I want to find the sum of a range of values in a table, but I want this range to be set by a lookup function. My data table has numerous entries for each value I want to lookup (say there are 21 matches of value "a", and then I want to sum up what corresponds to all those "a" values.). Any help? Let me know if you need more info. Thanks! |
#4
|
|||
|
|||
Try the more efficient:
=SUMIF(lookup_range,"a",value_range) ark wrote: fantastic, it works great. thanks. "Duke Carey" wrote: =sumproduct(--(lookup_range="a"),value_range) lookup_range MUST be the same size as value_range or this won't work. Thus it needs to be something like =sumproduct(--(A2:A200="a"),B2:B200) "ark" wrote: I want to find the sum of a range of values in a table, but I want this range to be set by a lookup function. My data table has numerous entries for each value I want to lookup (say there are 21 matches of value "a", and then I want to sum up what corresponds to all those "a" values.). Any help? Let me know if you need more info. Thanks! -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match function...random search? | Excel Worksheet Functions | |||
Return cell reference of lookup value | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Can I use formulas that return cell range ref. in charts X series | Charts and Charting in Excel |