Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
summing first 'n' values in a column or row | Excel Worksheet Functions | |||
Summing values within a matrix who are both in a column where theheaders are within a certain numerical range and in a row where the rowheaders are specified. | Excel Worksheet Functions | |||
summing up values in a column based on values in 3 other columns | Excel Programming | |||
summing last values in column | Excel Worksheet Functions | |||
Summing values within a range | Excel Discussion (Misc queries) |