Find sum with multiple conditions of 'top n values of a table'.

[Sarangsood]

Please check the attachment.

[Sarangsood]

Untitled.jpg This is the image.

[FDibbins]

Sorry for off-topic interjection:

Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

Thanks again for all your hard work here!

[Sarangsood]

@FDibbins got it.

I want to find the FORMULA for the sum of first 5 values of column D (Header "%") with following conditions :

1) Find top 20 values according to column B (Header "Weight")

2) Find the top 10 values of column C (Header "Vol") from the 20 values of point 1.

3) Find the sum of first 5 Values from column D.

Basically i want to sort the whole table (high to low), first by column B , then from top 20 values sort column C and finally find the sum of first 5 values of column D.

I am using the formula below but the problem is that the values keep changing in column B & C. So i need to find a formula in which it can find the top values in those columns.

Please Login or Register  to view this content.

[samba_ravi]

Sum of 20 Value from Column B

Please Login or Register  to view this content.

Sum of 10 Value from Column C

Please Login or Register  to view this content.

Sum of 5 Value from Column D

Please Login or Register  to view this content.

[bebo021999]

Try:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[Sarangsood]

The sum should be 41%.

If you check it by sorting it manually. You will first sort the whole table by column B (High to Low), then again sort column C but only till row 21 and finally sum of first 5 values of column D.

[Sarangsood]

Sum of 20 Value from Column B

Please Login or Register  to view this content.

Sum of 10 Value from Column C

Please Login or Register  to view this content.

Sum of 5 Value from Column D

Please Login or Register  to view this content.

i saw the individual formula. till column c its coming out just fine.

[samba_ravi]

FOR Sum of 5 Value from Column D
=sumproduct(large((b2:b30>=large(b2:b30,20))*(c2:c30>=large(c2:c30,10))*(d2:d30),row(indirect("1:10")))) change this one as
=sumproduct(large((b2:b30>=large(b2:b30,20))*(c2:c30>=large(c2:c30,10))*(d2:d30),row(indirect("1:5"))))

[Sarangsood]

FOR Sum of 5 Value from Column D
=sumproduct(large((b2:b30>=large(b2:b30,20))*(c2:c30>=large(c2:c30,10))*(d2:d30),row(indirect("1:10")))) change this one as
=sumproduct(large((b2:b30>=large(b2:b30,20))*(c2:c30>=large(c2:c30,10))*(d2:d30),row(indirect("1:5"))))

yes i did that but the answer is still not coming.

[samba_ravi]

How it was 41%,

it was 18.46% + 15.26% + 8.37% + 6.16% + 5.27% = 53.52%

[Sarangsood]

sir i 1st sorted the whole table from column B (largest to smallest). then i sorted the 1st 20 rows from column C.

then the 1st 5 values in column D comes out to be : 2.46% + 6.16% + 18.46% + 5.27% + 8.83% = 41.18%.

I have uploaded the file by how the desired result of 41.18% was achieved.

Thanks

[samba_ravi]

sorry I misunderstood your requirement use below array formula (Confirm with shift+Ctrl+Enter)

Please Login or Register  to view this content.

[Sarangsood]

@nflsales : thanks a lot