• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help Required .. To sum top..

kundanlal

Member
Hellow Friends..

Attached herewith file with requirement..

Can any one help here..

Thanks..

Kundanlal
 

Attachments

  • Finding sum of Top.xls
    285 KB · Views: 6
This should get you started. In H10:

=SUM(LARGE(IF($B$2:$B$3378=$G10,$C$2:$C$3378),ROW(INDIRECT("1:50"))))
 
Thanks for immediate response.. This solves half solution.

1. To "Sum Top" means out of Top 1-50, 51-100 etc. from largest to lowest

2. The important column is D in this file..

I wanted to have a total of First ToP say 1-50, Next Top 51-100 etc. .on the basis of Top Numbers appearing in colum D and at the same time for those numbers in cell C.. In this case.. for H10, the sum of IND tot is 1140427, and corresponding numbers in Ind Tot =32652... Thanks once again.
 
Please attach your workbook again with a few expected results entered manually.

My formula returns the top 50 results for Pune added together.

You can change the formula easily enough to look at D instead of C:

=SUM(LARGE(IF($B$2:$B$3378=$G10,$D$2:$D$3378),ROW(INDIRECT("1:50"))))

and for the next 50:

=SUM(LARGE(IF($B$2:$B$3378=$G10,$D$2:$D$3378),ROW(INDIRECT("51:100"))))

and so on. :)
 
Thanks.. Workbook attached.. with expected result..as shown in colour range.. i.e. for say yellow colour, Top 1-50 Range of Ind Total is shown, I need to have corresponding cell total of values appearing in Int Tot Range..

The formula you have suggested works Good.
 

Attachments

  • Finding sum of Top.xls
    289.5 KB · Views: 5
What EXACTLY does this mean?

I need to have corresponding cell total of values appearing in Int Tot Range..
 
Sorry, I don't understand. How do you arrive at this result?

Excel 2016 (Windows) 32 bit
G
H
38
Branch
Int Tot
39
PUNE
32652.6​
Sheet: AUM OCT 16
 
As shown in file, the file is sorted on the basis of Large to Small for Ind Tot.. (Column D) ..I filtered on the basis of Branch=Pune. The sum of Ind Tot for 1-50..etc matches with the output generated by using the formula you have suggested.

Now, using this filtered file only, for these Top 1-50.. etc.., I have taken a sum of corresponding range of Int Tot. (Column C)

Means For Top 1-50 Ind Tot (Column D) ( sum is generated by using your formula), I need to have sum of corresponding cell of Int Tot. (Column C) (In file I just just taken sum manually).
 
Try this:

=SUM(INDEX($C$2:$C$3378,MATCH(LARGE(IF($B$2:$B$3378=$G39,$D$2:$D$3378),ROW(INDIRECT("1:50"))),$D$2:$D$3378,0)))
 
It is retunring first value of Range C..
Top 1-50Next 50-100Next 100-150Rest
BranchInt TotIND TotInt TotIND TotInt TotIND TotInt TotIND Tot
Derived by FormulaPUNE
5672.2​
1140427.70​
148.2​
161149.70​
79.8​
90939.00​
17396​
197127.2​
ExpectedPUNE
32652.6​
1140427.70​
6975.5​
161149.70​
7146.9​
90939.00​
17396​
197127.2​
 
Did you enter it with CTRL+SHIFT+ENTER? It works here with just ENTER.

Excel 2016 (Windows) 32 bit
G
H
I
36
37
Top 1-50​
38
Branch
Int Tot
IND Tot
39
PUNE
32652.6​
1140427.70
Sheet: AUM OCT 16

Excel 2016 (Windows) 32 bit
H
I
39
=SUM(INDEX($C$2:$C$3378,MATCH(LARGE(IF($B$2:$B$3378=$G39,$D$2:$D$3378),ROW(INDIRECT("1:50"))),$D$2:$D$3378,0)))​
=SUM(LARGE(IF($B$2:$B$3378=$G39,$D$2:$D$3378),ROW(INDIRECT("1:50"))))
Sheet: AUM OCT 16
 
No Maam.. I tried in same manner as well as with Ctrl+Shift+Enter... I don't know but value changes.. In fact in case of Ind tot., the dervied figures matched only when I enter the forumula with Ctrl+Shift+Enter.. I am using MS Office 2007.... Is it possible to forward your file..
 
I will attach my file, which works for me in Office 365. If it doesn't work for you in 2007, then I am sorry, I can go no further with this. Maybe someone else will be able to adapt my suggestion for your very old version.
 

Attachments

  • AliGW Finding Sum of Top N.xls
    296.5 KB · Views: 3
1] Here's the formulae modification based on AliGW's file at post #.14, with revision highlighted in red color and suit for Excel below Office 365 version

H39:

=SUM(INDEX($C$2:$C$3378,N(IF(1,MATCH(LARGE(IF($B$2:$B$3378=$G39,$D$2:$D$3378),ROW($1:$50)),$D$2:$D$3378,0)))))

I39:

=SUM(LARGE(IF($B$2:$B$3378=$G39,$D$2:$D$3378),ROW($1:$50)))

All formulae to be in CSE array enter instead of just enter

2] The different results (Post #11 & Post #12) between Office 365 and lower Excel version in this example, reason maybe the worksheet is under Filter mode.

3] Please see attached file

Regards
Bosco
 

Attachments

  • Finding Sum of Top N(1).xls
    297 KB · Views: 3
Last edited:
Back
Top