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

2,225,861
975,000
467,600
340,000
295,000
250,000
210,000
208,800
200,000
200,000
197,420
188,235
182,000
160,000
133,400
104,400
100,000
100,000
95,000
87,000
75,000
71,250
69,500
63,800
62,000
60,000
50,000
50,000
34,800
19,720
15,000
11,600
645,000
586,872
493,750
421,400
348,000
127,680
81,000

<colgroup><col></colgroup><tbody>
</tbody>

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
1	Lower	Upper	Sum	Frequency
2	0	100,000	964,670	16
3	100,000	300,000	2,456,935	13
4	300,000	500,000	2,070,750	5
5	500,000	1,000,000	2,206,872	3
6	1,000,000	1,500,000	0	0
7	1,500,000	2,000,000	0	0
8	2,000,000	2,500,000	2,225,861	1
9	2,500,000	3,000,000	0	0
10	3,000,000	3,500,000	0	0
11	3,500,000	4,000,000	0	0
12	4,000,000	4,500,000	0	0
13	4,500,000	5,000,000	0	0
14	5,000,000	5,500,000	0	0
15	5,500,000		0	0
16			9,925,088
17	Data
18	2,225,861

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet28​

Worksheet Formulas Cell Formula C2 =SUMIFS($A$18:$A$55,$A$18:$A$55,">"&A2,$A$18:$A$55,"<="&B2) C15 =SUMIFS($A$18:$A$55,$A$18:$A$55,">"&A15) C16 =SUM(C2:C15) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula D2:D15 {=FREQUENCY(A18:A55,B2:B14)} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

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