Sum value between values

[Lammers73]

Hey there,

I am trying excel to sum values between fixed (value) brackets. So effectively calculating the sum of values between fixed numbers.
for example. How should I solve this?

Thanks for your help.

Book1
	A	B	C	D	E	F
4	data
5	Price	Volume		Price brackets	Price brackets	Sum value
6	150	41		0	100	xxx
7	388	11		101	200	xxx
8	101	55		201	300	xxx
9	94	22		301	400	xxx
10	231	13		401	500	xxx
11	440	50
Blad1

 

[J.Ty.]

Is this what you want?

Book1
	A	B	C	D	E	F	G
1		Price	Volume		Price brackets	Price brackets	Sum value
2	6	150	41		0	100	3854
3	7	388	11		101	200	1111
4	8	101	55		201	300	12705
5	9	94	22		301	400	0
6	10	231	13		401	500	5720
7	11	440	50
Sheet1

Cell Formulas
Range	Formula
G2:G6	G2	=SUMPRODUCT((B2:B7>=E2)*(B2:B7<=F2)*B2:B7*C2)

 

[CSmith]

Book1
	A	B	C	D	E	F
1	data
2	Price	Volume		Price brackets	Price brackets	Sum value
3	150	41		0	100	5050
4	388	11		101	200	15050
5	101	55		201	300	25050
6	94	22		301	400	35050
7	231	13		401	500	45050
8	440	50
MrExcel Help - Lammers73

Cell Formulas
Range	Formula
F3:F7	F3	=IF(INT((E3-D3+1)/2)=((E3-D3+1)/2),((E3-D3+1)/2)*(E3+D3),INT((E3-D3+1)/2)*(E3+D3)+INT((E3-D3+1)/2))

 

[J.Ty.]

Sorry, there was a typo in my formula:

Book1
	A	B	C	D	E	F	G
1		Price	Volume		Price brackets	Price brackets	Sum value
2	6	150	41		0	100	2068
3	7	388	11		101	200	5555
4	8	101	55		201	300	3003
5	9	94	22		301	400	0
6	10	231	13		401	500	22000
7	11	440	50
Sheet1

Cell Formulas
Range	Formula
G2:G6	G2	=SUMPRODUCT((B2:B7>=E2)*(B2:B7<=F2)*B2:B7*C2:C7)

 

[CSmith]

Not sure that's what they asked for...?

fixed (value) brackets

 

[Lammers73]

Thanks for taking timing replying.

What I want is this. I filled the cells manually. It sums the value between the corresponding brackets

Book1
	A	B	C	D	E	F	G
4	data
5	Price	Volume		Price brackets	Price brackets	Sum value
6	150	41		0	100	22	??
7	388	11		101	200	96	??
8	101	55		201	300	13	??
9	94	22		301	400	11	??
10	231	13		401	500	50	??
11	440	50
Blad1

 

[selvamg]

Hey there,

I am trying excel to sum values between fixed (value) brackets. So effectively calculating the sum of values between fixed numbers.
for example. How should I solve this?

Thanks for your help.

Book1
	A	B	C	D	E	F
4	data
5	Price	Volume		Price brackets	Price brackets	Sum value
6	150	41		0	100	xxx
7	388	11		101	200	xxx
8	101	55		201	300	xxx
9	94	22		301	400	xxx
10	231	13		401	500	xxx
11	440	50
Blad1

Do u mean...some thing liek = Sum(a1..a7) like this ?

 

[CSmith]

NVM I got it now...

 

[J.Ty.]

OK:

Book1
	A	B	C	D	E	F	G
2	6	150	41		0	100	22
3	7	388	11		101	200	96
4	8	101	55		201	300	13
5	9	94	22		301	400	11
6	10	231	13		401	500	50
7	11	440	50
Sheet1

Cell Formulas
Range	Formula
G2:G6	G2	=SUMIFS($C$2:$C$7,$B$2:$B$7,"<="&F2,$B$2:$B$7,">="&E2)

 

[Lammers73]

Yes got it!! Great, thanks.