# Help Required .. To sum top..

#### kundanlal

##### Member
Hellow Friends..

Attached herewith file with requirement..

Can any one help here..

Thanks..

Kundanlal

#### Attachments

• 285 KB Views: 6

#### AliGW

##### Active Member
What does "to sum top" mean? Please explain.

#### 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

• 289.5 KB Views: 5

#### 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-50 Next 50-100 Next 100-150 Rest Branch Int Tot IND Tot Int Tot IND Tot Int Tot IND Tot Int Tot IND Tot Derived by Formula PUNE 5672.2​ 1140427.70​ 148.2​ 161149.70​ 79.8​ 90939.00​ 17396​ 197127.2​ Expected PUNE 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

• 296.5 KB Views: 3

#### kundanlal

##### Member
Thank you very much for your support, I appreciate..

#### AliGW

##### Active Member
Hopefully someone with 2007 can complete the job for you.

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

Regards
Bosco

#### Attachments

• 297 KB Views: 3
Last edited:

#### kundanlal

##### Member
Brilliant Bosco Sir.. Thank you very much for the help extended by AliGW and you.. Very much appreciated..