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!

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

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!

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.