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

[jack]

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]

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]

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

 

[Matthew Herbert]

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$10>A1)*($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