Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |