Sum value between values

Lammers73

New Member
Joined
Jul 13, 2019
Messages
11
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
ABCDEF
4data
5PriceVolumePrice bracketsPrice bracketsSum value
6150410100xxx
738811101200xxx
810155201300xxx
99422301400xxx
1023113401500xxx
1144050
Blad1
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

J.Ty.

Well-known Member
Joined
Feb 4, 2012
Messages
1,109
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
  2. Web
Is this what you want?

Book1
ABCDEFG
1PriceVolumePrice bracketsPrice bracketsSum value
261504101003854
37388111012001111
481015520130012705
5994223014000
610231134015005720
71144050
Sheet1
Cell Formulas
RangeFormula
G2:G6G2=SUMPRODUCT((B2:B7>=E2)*(B2:B7<=F2)*B2:B7*C2)
 

CSmith

Well-known Member
Joined
Jan 13, 2020
Messages
686
Office Version
  1. 365
  2. 2010
  3. 2007
Platform
  1. Windows
  2. Mobile
  3. Web
Book1
ABCDEF
1data
2PriceVolumePrice bracketsPrice bracketsSum value
31504101005050
43881110120015050
51015520130025050
6942230140035050
72311340150045050
844050
MrExcel Help - Lammers73
Cell Formulas
RangeFormula
F3:F7F3=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.

Well-known Member
Joined
Feb 4, 2012
Messages
1,109
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
  2. Web
Sorry, there was a typo in my formula:
Book1
ABCDEFG
1PriceVolumePrice bracketsPrice bracketsSum value
261504101002068
37388111012005555
48101552013003003
5994223014000
6102311340150022000
71144050
Sheet1
Cell Formulas
RangeFormula
G2:G6G2=SUMPRODUCT((B2:B7>=E2)*(B2:B7<=F2)*B2:B7*C2:C7)
 

Lammers73

New Member
Joined
Jul 13, 2019
Messages
11
Thanks for taking timing replying.

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


Book1
ABCDEFG
4data
5PriceVolumePrice bracketsPrice bracketsSum value
615041010022??
73881110120096??
81015520130013??
9942230140011??
102311340150050??
1144050
Blad1
 

selvamg

New Member
Joined
Jul 30, 2019
Messages
8

ADVERTISEMENT

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
ABCDEF
4data
5PriceVolumePrice bracketsPrice bracketsSum value
6150410100xxx
738811101200xxx
810155201300xxx
99422301400xxx
1023113401500xxx
1144050
Blad1
Do u mean...some thing liek = Sum(a1..a7) like this ?
 

CSmith

Well-known Member
Joined
Jan 13, 2020
Messages
686
Office Version
  1. 365
  2. 2010
  3. 2007
Platform
  1. Windows
  2. Mobile
  3. Web
NVM I got it now...
 
Last edited:

J.Ty.

Well-known Member
Joined
Feb 4, 2012
Messages
1,109
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
  2. Web
OK:
Book1
ABCDEFG
2615041010022
373881110120096
481015520130013
59942230140011
6102311340150050
71144050
Sheet1
Cell Formulas
RangeFormula
G2:G6G2=SUMIFS($C$2:$C$7,$B$2:$B$7,"<="&F2,$B$2:$B$7,">="&E2)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,288
Messages
5,635,333
Members
416,855
Latest member
niha

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top