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

  • Thread starter Thread starter jack
  • Start date Start date
J

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.
 
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.)
 
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
 
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
Click to expand...

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
Click to expand...
that the arrays within the SUMPRODUCT function are all the same size.

Best,

Matthew Herbert
 
Back
Top