# Use a single formula to calculate the product of the top 10 cells with another column

1. ## Use a single formula to calculate the product of the top 10 cells with another column

I'm looking for advice on this question. I have two columns: A and B. Assume it is A1:A100, and I want to get the top 10 cells of column A and then multiple it with those values in column B. How can I make this possible in a single formula?   Register To Reply

2. ## Re: Use a single formula to calculate the product of the top 10 cells with another column

By top 10, do you mean the 10 with the highest values? What are "those values in column B"? Do you mean the values in column B that are in the same rows as the highest values in column A?

The best thing would be to attach a sample file and show us what result you're looking for.  Register To Reply

3. ## Re: Use a single formula to calculate the product of the top 10 cells with another column Originally Posted by 6StringJazzer By top 10, do you mean the 10 with the highest values? What are "those values in column B"? Do you mean the values in column B that are in the same rows as the highest values in column A?

The best thing would be to attach a sample file and show us what result you're looking for.
Yes, that is exactly right. Just multiply the largest 10 values in column A with the corresponding values in column B.

A sample data is attached now in the post.  Register To Reply

4. ## Re: Use a single formula to calculate the product of the top 10 cells with another column

Hi,

Maybe:

=SUMPRODUCT(A2:A21*ISNUMBER(MATCH(A2:A21,INDEX(LARGE(A2:A21,ROW(INDIRECT("1:10"))),,),0))*B2:B21)

Regards  Register To Reply

5. ## Re: Use a single formula to calculate the product of the top 10 cells with another column

There is something wrong. When using the formula, I get an answer of 2464.4492 but after sorting the values of column A in order then using =SUMPRODUCT((A1:A10)*(B1:B10)) I get an answer of 2913.468277 which agrees with the multiplication of the values then adding the products.  Register To Reply

6. ## Re: Use a single formula to calculate the product of the top 10 cells with another column

This awful formula agrees with my manual method.....there just has to be a better way.

Formula:  `Please Login or Register  to view this content.`  Register To Reply

7. ## Re: Use a single formula to calculate the product of the top 10 cells with another column

The formula given by XOR LX will be correct if A2 is changed to A1 and B2 is changed to B1 for the example file given.

Nice formula XOR LX!!  Register To Reply

8. ## Re: Use a single formula to calculate the product of the top 10 cells with another column

How do you want to handle duplicates? If there are ties on or around the boundary do you want to use more than 10 rows.....or should it be strictly 10? If the latter then how would you determine which rows to use (given that the column B values might be different for any duplicates)?

XOR LX's suggestion is doing the former (might use more than 10 values in case of duplicates). You can do that more easily with this formula

=SUMPRODUCT((A1:A20>=LARGE(A1:A20,10))+0,A1:A20,B1:B20)  Register To Reply

9. ## Re: Use a single formula to calculate the product of the top 10 cells with another column

Yes, of course. Very concise.

Regards  Register To Reply

10. ## Re: Use a single formula to calculate the product of the top 10 cells with another column

Thanks for the very useful formulae. This really helped   Register To Reply