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




SUMIF Greater than or equal to
Hi 
I am trying to determine the number of customers I get in a specific time range. My spreadsheet is as follows: Tab 1 gives customers and times: A B Time # Customers 6:05 1 7:30 50 8:15 20 etc. Tab 2 I calculate based on time ranges A B C Start End # of customers 6:01 6:30 need formula 6:31 7:00 7:01 7:30 etc. Does anyone have a good formula? I have tried a few and the latest one isn't working right: =SUMIFS(Tab1!b2:b200,Tab1!a4:a200,">="&A2,Tab1!a4: a200,"<="&B2) Any suggestions.... Thanks!  en 
Ads 
#2




SUMIF Greater than or equal to
Hi
Look at this: =SUMIF(Tab1!A2:A20,">="&A2,Tab1!B2:B20)SUMIF(Tab1!A2:A20,">"&B2,Tab1! B2:B20) Regards, Per On 29 Okt., 00:47, envy > wrote: > Hi  > I am trying to determine the number of customers I get in a specific time > range. *My spreadsheet is as follows: > Tab 1 gives customers and times: > A * * * * * * B > Time * *# Customers > 6:05 * * *1 > 7:30 * * 50 > 8:15 * * 20 > etc. > > Tab 2 I calculate based on time ranges > A * * * *B * * * * * * *C > Start *End * # of customers > 6:01 *6:30 * * *need formula > 6:31 *7:00 * * * * > 7:01 *7:30 * * * * > etc. > > Does anyone have a good formula? *I have tried a few and the latest one > isn't working right: > =SUMIFS(Tab1!b2:b200,Tab1!a4:a200,">="&A2,Tab1!a4: a200,"<="&B2) > Any suggestions.... > Thanks! >  > en 
#3




SUMIF Greater than or equal to
I think the arrays need to be the same size. In one case you have from row 2
to row 200, and in others it's row 4 to 200. HTH, Barb Reinhardt "envy" wrote: > Hi  > I am trying to determine the number of customers I get in a specific time > range. My spreadsheet is as follows: > Tab 1 gives customers and times: > A B > Time # Customers > 6:05 1 > 7:30 50 > 8:15 20 > etc. > > Tab 2 I calculate based on time ranges > A B C > Start End # of customers > 6:01 6:30 need formula > 6:31 7:00 > 7:01 7:30 > etc. > > Does anyone have a good formula? I have tried a few and the latest one > isn't working right: > =SUMIFS(Tab1!b2:b200,Tab1!a4:a200,">="&A2,Tab1!a4: a200,"<="&B2) > Any suggestions.... > Thanks! >  > en 
#4




SUMIF Greater than or equal to
Another way using sumproduct()
=SUMPRODUCT(('Tab1'!A2:A200>=A2)*('Tab1'!A2:A200<= B2),'Tab1'!B2:B200) If this post helps click Yes  Jacob Skaria "envy" wrote: > Hi  > I am trying to determine the number of customers I get in a specific time > range. My spreadsheet is as follows: > Tab 1 gives customers and times: > A B > Time # Customers > 6:05 1 > 7:30 50 > 8:15 20 > etc. > > Tab 2 I calculate based on time ranges > A B C > Start End # of customers > 6:01 6:30 need formula > 6:31 7:00 > 7:01 7:30 > etc. > > Does anyone have a good formula? I have tried a few and the latest one > isn't working right: > =SUMIFS(Tab1!b2:b200,Tab1!a4:a200,">="&A2,Tab1!a4: a200,"<="&B2) > Any suggestions.... > Thanks! >  > en 
#5




SUMIF Greater than or equal to
Works OK for me when you make the ranges the same size:
=SUMIFS('Tab1'!B$2:B$200,'Tab1'!A$2:A$200,">="&A2, 'Tab1'!A$2:A$200,"<="&B2) Note that in Excel 2007 there is a cell address TAB1 so in order for Excel to know you're referencing a sheet named Tab1 and not the cell address TAB1 the sheet name Tab1 needs to be enclosed in single qoutes: 'Tab1'.  Biff Microsoft Excel MVP "envy" > wrote in message ... > Hi  > I am trying to determine the number of customers I get in a specific time > range. My spreadsheet is as follows: > Tab 1 gives customers and times: > A B > Time # Customers > 6:05 1 > 7:30 50 > 8:15 20 > etc. > > Tab 2 I calculate based on time ranges > A B C > Start End # of customers > 6:01 6:30 need formula > 6:31 7:00 > 7:01 7:30 > etc. > > Does anyone have a good formula? I have tried a few and the latest one > isn't working right: > =SUMIFS(Tab1!b2:b200,Tab1!a4:a200,">="&A2,Tab1!a4: a200,"<="&B2) > Any suggestions.... > Thanks! >  > en 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Greater/Less Than or Equal To  Hoov  Excel Discussion (Misc queries)  5  May 14th 09 05:54 PM 
sumif date is greater than or equal chosen date  Gary  Excel Discussion (Misc queries)  2  September 19th 08 09:51 AM 
IF with Equal to or Greater than  sonicj  Excel Discussion (Misc queries)  4  May 1st 08 09:02 PM 
Greater than or equal to (Plus or minus)  Brandon  Excel Discussion (Misc queries)  2  July 18th 06 11:34 PM 
First occurance greater than or equal to a specified value  [email protected]  Excel Worksheet Functions  3  February 5th 06 07:34 AM 