Look up values in a range, then summing from another column
 

I would like to find every instance of a number within a given range
and then sum up values from a designated column on the same row.

Look up values in a range, then summing from another column
 

Maybe something like:

=sumif(a:a,333,b:b)

(Sum the values in column B where the cell in column A is equal to 333.)

jack wrote:

I would like to find every instance of a number within a given range
and then sum up values from a designated column on the same row.

--

Dave Peterson

Look up values in a range, then summing from another column
 

Here is some more detail:

The range values are in separate cells. For example, A2 would have 0
and B2 would have 2. I would like to look at the cells with the ranges
to determine what values to look for and then sum the $ value for all
cells within the range.

Based on the example below the value to return for the range 0-2 years
is $358, 2-4 is $200, and 4-6 is $426


Years(Range)

0 2
3 4
5 6


Year $
5 552
1 358
3 200
5 685
6 189

Look up values in a range, then summing from another column
 

On Jul 22, 9:07*pm, jack wrote:
Here is some more detail:

The range values are in separate cells. For example, A2 would have 0
and B2 would have 2. I would like to look at the cells with the ranges
to determine what values to look for and then sum the $ value for all
cells within the range.

Based on the example below the value to return for the range 0-2 years
is $358, 2-4 is $200, and 4-6 is $426

Years(Range)

0 * * * 2
3 * * * 4
5 * * * 6

Year * *$
5 * * * 552
1 * * * 358
3 * * * 200
5 * * * 685
6 * * * 189

Jack,

SUMPRODUCT should do the trick. Assuming the following: 0 in A1, 2 in
B1; 2 in A2, 4 in B2; 4 in A3, 6 in B3 and Year 5 in A6, 552 in B6; 1
in A7, 358 in B7, and so on. Use the following formula in C1 and copy
down to C3:

=SUMPRODUCT(($A$6:$A$10A1)*($A$6:$A$10<B1)*$B$6:$ B$10)

Feel free to adjust your numbers in A1:B3 and feel free to adjust the
, < signs to be some combination of , < or =, <=. Just be sure
that the arrays within the SUMPRODUCT function are all the same size.

Best,

Matthew Herbert