Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Formula help - sumif (possibly)
I am looking for a formula or function that will sum columns based on meeting
specific criteria. for example, I have an age table that with a minimum age column then a maximum age column. I need to be able to enter an age in to a seperate cell and have Excel sum based on the ages. Min Max Non Tob Spouse Child EE ADD Sp ADD Ch ADD 15 24 0.46 0.69 0.535 0.679 0.266 0.14 0.072 25 29 0.53 0.78 0.45 0.679 30 34 0.65 0.97 0.49 0.679 35 39 0.9 1.46 0.675 0.679 40 44 1.24 2.19 0.97 0.679 45 49 1.99 3.48 1.52 0.679 50 54 3.05 5.87 2.365 0.679 55 59 4.98 8.28 3.63 0.679 60 64 7.95 12.37 6.205 0.679 65 69 13.95 20.68 10.595 0.679 70 74 25.17 36.36 18.88 0.679 79 50.97 65.77 37.815 0.679 |
#2
|
|||
|
|||
Using the sample data you provided- headers in row 1 from column A ~ I
and the criterion and data in A2:I13, I got a solution by adding two elements. The first is a total column, J, that sums your data in advance. The next is a max age number in cell B13: it was easier to add the max age of 999 rather than figure out a solution that left that cell blank. By using 999 you are effectively providing an unlimited ceiling age, but still one the formula logic can work with. If you'll enter your target age in cell A16, enter this formula in B16: =SUMPRODUCT(--(A16=$A$2:$A$13),--(A16<=$B$2:$B$13),$J$2:$J$13) The formula checks your indicated age to see where it fits in the grid, and returns the total from column J. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula works in some cells, doesn't in other | New Users to Excel | |||
Modify SumIF... Array Formula | Excel Worksheet Functions | |||
IF & VLOOKUP FORMULA | Excel Worksheet Functions | |||
Relative Indirect Formula Referencing? | Excel Worksheet Functions | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions |