Help Required .. To sum top..

[kundanlal]

Hellow Friends..

Attached herewith file with requirement..

Can any one help here..

Thanks..

Kundanlal

 

[AliGW]

What does "to sum top" mean? Please explain.

 

[AliGW]

This should get you started. In H10:

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

 

[kundanlal]

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]

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]

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.

 

[AliGW]

What EXACTLY does this mean?

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

 

[AliGW]

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]

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]

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]

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]

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]

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]

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.

 

[kundanlal]

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

 

[AliGW]

Hopefully someone with 2007 can complete the job for you.

 

[bosco_yip]

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

 

[kundanlal]

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