Two formulae to calculate the sum of top 3 cells for a category?

[billj]

Dear all,

I used two different formulae to calculate the sum of top 3 cells for a category. Text values or errors are both taken into account. The two formulae used are:

=SUM(LARGE(IF(A2:A11="Medium",IF(ISNUMBER(B2:B11),B2:B11,""),""),ROW(INDIRECT(("1:3")))),0)

=SUM(IF((A2:A11="Medium")*(RANK(B2:B11,B2:B11)<=3),B2:B11,0))

The first one works well, but the second one does not. I tried to repair the second one, but failed

I attached the sample here. Can you please have a look to modify formula 2?

Thanks a lot in advance.

[Ace_XL]

Your first formula can be shortened to
=SUM(LARGE(IF(A2:A11="Medium",IF(ISNUMBER(B2:B11),B2:B11,""),""),{1,2,3}))

An reason, why you want to use the RANK function?

[billj]

Your first formula can be shortened to
=SUM(LARGE(IF(A2:A11="Medium",IF(ISNUMBER(B2:B11),B2:B11,""),""),{1,2,3}))

An reason, why you want to use the RANK function?

Hi Ace_XL, thanks for the idea to shorten the formula.

In terms of the RANK() function, it is just a curiosity to get it work. But if it is not better than the first one, then I just forget about it.

[martindwilson]

why not just stick with the one that works?

[XOR LX]

Hi,

Forgive me for asking, but why do you need two formulas?

There are several things wrong with your second formula:

1) You are asking it to return the sum of values when the Quality is "Medium" and the Rank is <=3. However, this Rank you are taking over the entire range, NOT the subset of those which have a Quality of "Medium". So, for example, the 2nd ranked value from amongst those with a Quality of "Medium" (492.98) is actually ranked 4th out of the larger set and so will be excluded from your summation.

2) You have not excluded potential errors or text values (N/A, #DIV/0! - this is the function of the IF(ISNUMBER) part of the other formula), and the RANK function will not accept/ignore these.

Regards

[billj]

Thanks for the many great suggestions. I will now stick to the first formula