• 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..

AliGW

Active Member
This should get you started. In H10:

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

kundanlal

Member
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.
 

AliGW

Active Member
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. :)
 

kundanlal

Member
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

AliGW

Active Member
What EXACTLY does this mean?

I need to have corresponding cell total of values appearing in Int Tot Range..
 

AliGW

Active Member
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
 

kundanlal

Member
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).
 

AliGW

Active Member
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)))
 

kundanlal

Member
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​
 

AliGW

Active Member
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
 

kundanlal

Member
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..
 

AliGW

Active Member
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

bosco_yip

Excel Ninja
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

Last edited:
Top