You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Rod" wrote in message
...
I have never used the ROWS function before. I'll have to look at it for
future ref.. In any case, your thought of using SUMIF is what got me
thinking.

Thanks!

"T. Valko" wrote:

Hmmm....

These 2 formulas are essentially the same:

=SUMIF(G$47:G$51,"=1",E$47:E$51)
=SUMIF(G$47:G$51,ROWS($1:1),E$47:E$51)

I don't see why one works for you and the other doesn't but if you got
something that does work that's all that counts.

screencap:

http://img401.imageshack.us/img401/3738/sumif1st6.jpg


--
Biff
Microsoft Excel MVP


"Rod" wrote in message
...
The change did not work. I played with it and did:
=SUMIF(G$47:G$51,"=1",E$47:E$51) and it works.

Thanks for your great help!

"T. Valko" wrote:

The ranking is working perfectly, but the summing is coming up 0.

Then the values in D47:D51 are TEXT and are not numeric numbers.

Try this:

Select an empty cell anywhere on your sheet that has the DEFAULT
format
of
GENERAL
Copy that empty cell: EditCopy
Select the range D47:D51
Then: EditPaste SpecialAddOK

--
Biff
Microsoft Excel MVP


"Rod" wrote in message
...
I am adapting this to the location of the data in my sheet.
Everything
looks
good until I get to the ROWS function. For me, "Fund Education"...
begins
in
B47. The second formulat looks like:
=IF(ROWS($47:47)<=MAX(E$47:E$51),SUMIF(E$47:E$51,R OWS($1:1),D$47:D$51),"").
I have a flag for other use in column D, so starting in B47 I have:
Fund Education Rider 15 $110,000 $25.82
Pay Off Consumer Debt Rider 10 $- $-
Provide Survivor Income Replacement Rider 10 $30,000 $5.05
Pay Off Mortgage Base 20 $100,000
$33.27
Pay Final Expenses Base 20 $10,000
$3.33
The ranking is working perfectly, but the summing is coming up 0.

Thanks

"Rod" wrote:

Will this work if

Fund Education 15
Pay Off Consumer Debt 10
Provide Survivor Income Replacement 20
Pay Off Mortgage 35
Pay Final Expenses 30

"Teethless mama" wrote:

Take a look MEDIAN Function in help menu

"Rod" wrote:

I have 5 categories with years associated with the categories:

Fund Education 15
Pay Off Consumer Debt 10
Provide Survivor Income Replacement 10
Pay Off Mortgage 20
Pay Final Expenses 20

I would like to order/rank these from greatest to least:

Fund Education 15 2
Pay Off Consumer Debt 10 3
Provide Survivor Income Replacement 10 3
Pay Off Mortgage 20 1
Pay Final Expenses 20 1

I will need to later add those years of the same order/rank. I
tried
sumif
using max and min but could not figure out how to do the middle
values; is
there a better way to do this?