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
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

G​

[/th][th]

H​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

38​

[/td][td]Branch[/td][td=bgcolor:#FFFF00]

Int Tot​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

39​

[/td][td=bgcolor:#E4DFEC]PUNE[/td][td=bgcolor:#FFFF00]

32652.6​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: AUM OCT 16[/td][/tr][/table]

 

[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
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

36​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

37​

[/td][td][/td][td=bgcolor:#FFFF00]

Top 1-50​

[/td][td=bgcolor:#FFFF00]

​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

38​

[/td][td]Branch[/td][td=bgcolor:#FFFF00]

Int Tot​

[/td][td=bgcolor:#FFFF00]

IND Tot​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

39​

[/td][td=bgcolor:#E4DFEC]PUNE[/td][td=bgcolor:#FFFF00]

32652.6​

[/td][td=bgcolor:#FFFF00]

1140427.70​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: AUM OCT 16[/td][/tr][/table]

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

H​

[/th][th]

I​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

39​

[/td][td=bgcolor:#FFFF00]

=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)))​

[/td][td=bgcolor:#FFFF00]

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

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: AUM OCT 16[/td][/tr][/table]

 

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