Sum of tray between various amount salb

[Attique Tariq]

I want to sum values between various amounts slabs i.e.


Sum between Slabs Total sum
0-100,000 ?
100,000-300,000 ?
300,000-500,000 ?
500,000-1,000,000 ?
1,000,000-1,500,000 ?
15,000,00-2,000,000 ?
2,000,000-2,500,000 ?
2,500,000-3,000,000 ?
3,000,000-3,500,000 ?
3,500,000-4,000,000 ?
4,000,000-4,500,000 ?
4,500,000-5,000,000 ?
5,000,000-5,500,000 ?
Greater than 5,500,000 ?

Array Value
[TABLE="width: 76"]
<colgroup><col></colgroup><tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD]2,225,861[/TD]
[/TR]
[TR]
[TD]975,000[/TD]
[/TR]
[TR]
[TD]467,600[/TD]
[/TR]
[TR]
[TD]340,000[/TD]
[/TR]
[TR]
[TD]295,000[/TD]
[/TR]
[TR]
[TD]250,000[/TD]
[/TR]
[TR]
[TD]210,000[/TD]
[/TR]
[TR]
[TD]208,800[/TD]
[/TR]
[TR]
[TD]200,000[/TD]
[/TR]
[TR]
[TD]200,000[/TD]
[/TR]
[TR]
[TD]197,420[/TD]
[/TR]
[TR]
[TD]188,235[/TD]
[/TR]
[TR]
[TD]182,000[/TD]
[/TR]
[TR]
[TD]160,000[/TD]
[/TR]
[TR]
[TD]133,400[/TD]
[/TR]
[TR]
[TD]104,400[/TD]
[/TR]
[TR]
[TD]100,000[/TD]
[/TR]
[TR]
[TD]100,000[/TD]
[/TR]
[TR]
[TD]95,000[/TD]
[/TR]
[TR]
[TD]87,000[/TD]
[/TR]
[TR]
[TD]75,000[/TD]
[/TR]
[TR]
[TD]71,250[/TD]
[/TR]
[TR]
[TD]69,500[/TD]
[/TR]
[TR]
[TD]63,800[/TD]
[/TR]
[TR]
[TD]62,000[/TD]
[/TR]
[TR]
[TD]60,000[/TD]
[/TR]
[TR]
[TD]50,000[/TD]
[/TR]
[TR]
[TD]50,000[/TD]
[/TR]
[TR]
[TD]34,800[/TD]
[/TR]
[TR]
[TD]19,720[/TD]
[/TR]
[TR]
[TD]15,000[/TD]
[/TR]
[TR]
[TD]11,600[/TD]
[/TR]
[TR]
[TD]645,000[/TD]
[/TR]
[TR]
[TD]586,872[/TD]
[/TR]
[TR]
[TD]493,750[/TD]
[/TR]
[TR]
[TD]421,400[/TD]
[/TR]
[TR]
[TD]348,000[/TD]
[/TR]
[TR]
[TD]127,680[/TD]
[/TR]
[TR]
[TD]81,000[/TD]
[/TR]
</tbody>[/TABLE]



Can i do it through histogram or some other function?

 

[63falcondude]

A histogram will give you the frequency or count, you want the sum.

Let's say that your array is in column A. You can use a formula such as =SUMIFS(A:A,A:A,">=0",A:A,"<100000"). This will take the sum of everything in column A that is greater than or equal to 0 and less than 100000.

Then =SUMIFS(A:A,A:A,">=100000",A:A,"<300000")
Use this until the last slab (greater than 5,500,000). For this one use =SUMIF(A:A,A:A,">5500000")

 

[DRSteele]

I'm not sure whether the 'Array Value' is just a range of cells or is the result of another formula. If it's just a range, this might do it for you. Copy C2 row 14; C15 is different. The formula in D2:D15 must be entered as an array. Your data goes down to row 55.

	A	B	C	D

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFF2CC"]Lower[/TD]
[TD="bgcolor: #FFF2CC"]Upper[/TD]
[TD="bgcolor: #FFF2CC"]Sum[/TD]
[TD="bgcolor: #FFF2CC"]Frequency[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100,000[/TD]
[TD="bgcolor: #E2EFDA, align: right"]964,670[/TD]
[TD="bgcolor: #E2EFDA, align: right"]16[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]100,000[/TD]
[TD="align: right"]300,000[/TD]
[TD="bgcolor: #E2EFDA, align: right"]2,456,935[/TD]
[TD="bgcolor: #E2EFDA, align: right"]13[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]300,000[/TD]
[TD="align: right"]500,000[/TD]
[TD="bgcolor: #E2EFDA, align: right"]2,070,750[/TD]
[TD="bgcolor: #E2EFDA, align: right"]5[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]500,000[/TD]
[TD="align: right"]1,000,000[/TD]
[TD="bgcolor: #E2EFDA, align: right"]2,206,872[/TD]
[TD="bgcolor: #E2EFDA, align: right"]3[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1,000,000[/TD]
[TD="align: right"]1,500,000[/TD]
[TD="bgcolor: #E2EFDA, align: right"]0[/TD]
[TD="bgcolor: #E2EFDA, align: right"]0[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1,500,000[/TD]
[TD="align: right"]2,000,000[/TD]
[TD="bgcolor: #E2EFDA, align: right"]0[/TD]
[TD="bgcolor: #E2EFDA, align: right"]0[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]2,000,000[/TD]
[TD="align: right"]2,500,000[/TD]
[TD="bgcolor: #E2EFDA, align: right"]2,225,861[/TD]
[TD="bgcolor: #E2EFDA, align: right"]1[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]2,500,000[/TD]
[TD="align: right"]3,000,000[/TD]
[TD="bgcolor: #E2EFDA, align: right"]0[/TD]
[TD="bgcolor: #E2EFDA, align: right"]0[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]3,000,000[/TD]
[TD="align: right"]3,500,000[/TD]
[TD="bgcolor: #E2EFDA, align: right"]0[/TD]
[TD="bgcolor: #E2EFDA, align: right"]0[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]3,500,000[/TD]
[TD="align: right"]4,000,000[/TD]
[TD="bgcolor: #E2EFDA, align: right"]0[/TD]
[TD="bgcolor: #E2EFDA, align: right"]0[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]4,000,000[/TD]
[TD="align: right"]4,500,000[/TD]
[TD="bgcolor: #E2EFDA, align: right"]0[/TD]
[TD="bgcolor: #E2EFDA, align: right"]0[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]4,500,000[/TD]
[TD="align: right"]5,000,000[/TD]
[TD="bgcolor: #E2EFDA, align: right"]0[/TD]
[TD="bgcolor: #E2EFDA, align: right"]0[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]5,000,000[/TD]
[TD="align: right"]5,500,000[/TD]
[TD="bgcolor: #E2EFDA, align: right"]0[/TD]
[TD="bgcolor: #E2EFDA, align: right"]0[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]5,500,000[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #C6E0B4, align: right"]0[/TD]
[TD="bgcolor: #C6E0B4, align: right"]0[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA, align: right"]9,925,088[/TD]
[TD="bgcolor: #E2EFDA, align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #FFF2CC"]Data[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]2,225,861[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet28​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=SUMIFS($A$18:$A$55,$A$18:$A$55,">"&A2,$A$18:$A$55,"<="&B2)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C15[/TH]
[TD="align: left"]=SUMIFS($A$18:$A$55,$A$18:$A$55,">"&A15)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C16[/TH]
[TD="align: left"]=SUM(C2:C15)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D2:D15[/TH]
[TD="align: left"]{=FREQUENCY(A18:A55,B2:B14)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[Attique Tariq]

Thank you very much it really help

 

[DRSteele]

You're welcome.

 

[Nishant94]

Excel 2016
	A	B	C	D	E	F	G
1		22,25,861		Lower Limit	Upper Limit	Total	Count
2		9,75,000		0	1,00,000	8,45,670	15
3		4,67,600		1,00,000	3,00,000	26,56,935	15
4		3,40,000		3,00,000	5,00,000	20,70,750	5
5		2,95,000		5,00,000	10,00,000	22,06,872	3
6		2,50,000		10,00,000	15,00,000	0	0
7		2,10,000		15,00,000	20,00,000	0	0
8		2,08,800		20,00,000	25,00,000	22,25,861	1
9		2,00,000		25,00,000	30,00,000	0	0
10		2,00,000		30,00,000	35,00,000	0	0
11		1,97,420		35,00,000	40,00,000	0	0
12		1,88,235		40,00,000	45,00,000	0	0
13		1,82,000		45,00,000	50,00,000	0	0
14		1,60,000		50,00,000	55,00,000	0	0
15		1,33,400			55,00,000	0	0
16		1,04,400
17		1,00,000		Total		1,00,06,088	39
18		1,00,000
19		95,000
20		87,000
21		75,000
22		71,250
23		69,500
24		63,800
25		62,000
26		60,000
27		50,000
28		50,000
29		34,800
30		19,720
31		15,000
32		11,600
33		6,45,000
34		5,86,872
35		4,93,750
36		4,21,400
37		3,48,000
38		1,27,680
39		81,000
40
41	Total	1,00,06,088
Sheet14

Cell Formulas
Range	Formula
F2	{=SUM(IF($B$1:$B$39D2="",9.999999E+307,E2),$B$1:$B$39,0))-SUM(F$1:F1)}
G2	{=SUM(IF($B$1:$B$39D2="",9.999999E+307,E2),1,0))-SUM(G$1:G1)}
Press CTRL+SHIFT+ENTER to enter array formulas.