Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 28th 09, 11:47 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2009
Posts: 4
Default 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   Report Post  
Old October 29th 09, 12:00 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2008
Posts: 703
Default 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   Report Post  
Old October 29th 09, 12:15 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,355
Default 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   Report Post  
Old October 29th 09, 12:36 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2009
Posts: 8,521
Default 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   Report Post  
Old October 29th 09, 12:56 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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


All times are GMT +1. The time now is 01:52 AM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017