Ranking on cell value and getting number of unique value

Hello friends,

I am trying to do the following in my investment portfolio, need help on a couple of things. Pls see attached excel file.

There are 2 sheets, with 2 different lists of stocks.

Column D = Industry Name
Column E = Stock Name
Column G = Points based on internal analysis

Cell D1 = Formula to get number of unique industries
Cell E1 = COUTNA formula to get number of stocks

Column I = Ranking based on values in column G

Here is where I need help:

#1

Cell D1 = How do I edit this formula so that it can be replicated across sheets with different number of stocks so that I get the right value?

If you notice, Sheet 1 has 53 stocks and Sheet 2 has 46 stocks. I have had to edit the formulas according to number of stocks

Sheet 1 > Cell D1 = {=SUM(1/COUNTIF($D$3:$D$55,$D$3:$D$55))}

Sheet 2 > Cell D1 = {=SUM(1/COUNTIF($D$3:$D$48,$D$3:$D$48))}

If I use the Sheet 1 formula in Sheet 2, it gives an error because the number of stocks is 46 compared to 53 rows...

Let us work with the assumption that there will be a maximum of 200 stocks (rows with sector names)


#2

I need to put a ranking formula in cell I1 and copy it down till the last stock in the portfolio. Here again, I need to keep adjusting the row reference.

Sheet 1 > Cell I1 = RANK(G3,$G$3:$G$55,0)

Sheet 2 > Cell I1 = RANK(G3,$G$3:$G$48,0)

How do I edit the formula in cell I1 so that it automatically takes the row reference by starting from $G$3 to row number based on value in cell E1?

Sheet 1 would be: $G$3:$G$55 (starting row = 3, number of stocks = 53... so it would be 3 + 53 - 1 = 55)

Sheet 2 would be: $G$3:$G$48 (starting row = 3, number of stocks = 46... so it would be 3 + 48 - 1 = 48)

... and the formula uses the COUNTIF function if there are duplicate values in column G

Many thanks in advance...

Regards,
Navin