If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#1




How to use SUMIF to return sums between two values located in cells
I have two rows of data where I would like to use SUMIF to look at the first row data (Row A) , and sum the values in the second row (Row B) if values are between those found in two cells (Rows C and D). I intend to use the resulting bin values to compare different data sets, and create a histogram like plots. There will be ~20,000 individual output bins in the real data set, so doing it manually would be problematic. Example Data Row A, B: A1= 1 , B1= 3 A2= 1.5, B2=3 A3= 2, B3 =5 A4= 2.2, B4 = 10 A5= 3, B4= 2 Row C, D, E C1=0, D1=1, E1= SUMIF A between C1 and less than D1 then Sum B (ANS: 0) C2=1, D2=2, E2= SUMIF A between C2 and less than D2 then Sum B (ANS: 6) C3=2, D3=3, E3= SUMIF A between C3 and less than D3 then Sum B (ANS: 15) C4=3, D4=4, E4= SUMIF A between C4 and less than D4 then Sum B (ANS: 2) C5=4, D5=5, E5= SUMIF A between C5 and less than D5 then Sum B (ANS: 0) C6=5, D6=6, E6= SUMIF A between C6 and less than D6 then Sum B (ANS: 0) Thank you for any help you can provide.  ScottBerger  ScottBerger's Profile: http://www.excelforum.com/member.php...o&userid=16553 View this thread: http://www.excelforum.com/showthread...hreadid=314926 
Ads 
#2




Hi
try =SUMIF(A:A,">=" &C1)SUMIF(A:A,">" & D1) "ScottBerger" wrote: > > I have two rows of data where I would like to use SUMIF to look at the > first row data (Row A) , and sum the values in the second row (Row B) > if values are between those found in two cells (Rows C and D). I intend > to use the resulting bin values to compare different data sets, and > create a histogram like plots. There will be ~20,000 individual > output bins in the real data set, so doing it manually would be > problematic. > > Example Data > > Row A, B: > > A1= 1 , B1= 3 > A2= 1.5, B2=3 > A3= 2, B3 =5 > A4= 2.2, B4 = 10 > A5= 3, B4= 2 > > Row C, D, E > > C1=0, D1=1, E1= SUMIF A between C1 and less than D1 then Sum B (ANS: > 0) > C2=1, D2=2, E2= SUMIF A between C2 and less than D2 then Sum B (ANS: > 6) > C3=2, D3=3, E3= SUMIF A between C3 and less than D3 then Sum B (ANS: > 15) > C4=3, D4=4, E4= SUMIF A between C4 and less than D4 then Sum B (ANS: > 2) > C5=4, D5=5, E5= SUMIF A between C5 and less than D5 then Sum B (ANS: > 0) > C6=5, D6=6, E6= SUMIF A between C6 and less than D6 then Sum B (ANS: > 0) > > Thank you for any help you can provide. > > >  > ScottBerger >  > ScottBerger's Profile: http://www.excelforum.com/member.php...o&userid=16553 > View this thread: http://www.excelforum.com/showthread...hreadid=314926 > > 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
How do I lookup and return different values when the lookup value.  kg  Excel Discussion (Misc queries)  1  January 20th 05 12:53 AM 
LOOKUP FUNCTION WITH SUMS VALUES  Jamesy  Excel Discussion (Misc queries)  3  January 10th 05 03:03 PM 
delete values in several cells without deleting the formulas  dranreb  Excel Discussion (Misc queries)  4  December 9th 04 01:15 AM 
How to add a button to restore all altered cells original values?  Dawnybros  Excel Discussion (Misc queries)  2  December 2nd 04 04:35 PM 
How to look up and return multiple values  Wendy  Excel Worksheet Functions  3  November 3rd 04 04:32 PM 