Home 
Search 
Today's Posts 
#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 
#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 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Greater/Less Than or Equal To  Excel Discussion (Misc queries)  
sumif date is greater than or equal chosen date  Excel Discussion (Misc queries)  
IF with Equal to or Greater than  Excel Discussion (Misc queries)  
Greater than or equal to (Plus or minus)  Excel Discussion (Misc queries)  
First occurance greater than or equal to a specified value  Excel Worksheet Functions 