Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-24-2012, 12:50 AM
excelledsoftware excelledsoftware is offline formula help Windows 7 64bit formula help Office 2003
IT Specialist
formula help
 
Join Date: Jan 2012
Location: Utah
Posts: 455
excelledsoftware will become famous soon enough
Default formula help

I am usually really good at figuring out formulas but I am kind of stuck on this one.



I have a HUGE spreadsheet that I use to complete a schedule for employees. They can be scheduled in different areas at different times. I am trying to write a formula that will count the amount of people in an area that start at or before a certain time.

Example in cells a11 through c15
A10=AREA B10=Shift Start C10=Shift End
A11=DD B11=0600 C11=1400
A12=DD B12=1400 C12=1900
A13=hr B13=0600 C13=1200
A14=DD B14=0700 C14=1500
A15=rr B15=1700 C15=2200

So you probably figured out the columns b and c are in military time format.

I am trying to write a formula that will count all of the people scheduled in area DD that are scheduled at or before 0700. I will be writing this formula for each area code and will make another one for associates that close (leave last)

I am aware of the sumif, and countif but they only give me the ability of 1 criteria. I want to stay away from VBA unless I have to and I would like to not have to use the sumproduct formula since it slows things down when there are alot of them. (we are talking around 400 formulas)

Thanks
Reply With Quote
  #2  
Old 03-24-2012, 05:08 AM
Catalin.B Catalin.B is offline formula help Windows Vista formula help Office 2010 32bit
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

The bormula will be based on SUMPRODUCT, but you must upload a sample data to see the format used, especially for that military time, is it entered as text, or the column is formatted as date "hhmm" ?
Reply With Quote
  #3  
Old 03-24-2012, 05:49 AM
Catalin.B Catalin.B is offline formula help Windows Vista formula help Office 2010 32bit
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

This formula assumes that you entered time in a cell formatted as text, and in G2 is the refference time, in format 16:00 PM
=SUMPRODUCT((TIME(LEFT(B11:B15;2);RIGHT(B11:B15;2) ;0)<=G2)*1)
Or, if you keep the same format in G2, simply: =SUMPRODUCT((B11:B15<=G2)*1)
To take into account the area, entered in F2: =SUMPRODUCT((TIME(LEFT(B11:B15;2);RIGHT(B11:B15;2) ;0)<=G2)*(A11:A15=F2))
Note: it is not an array formula, confirmed with CSE, so so it is not consuming so much resources. For another approach, using COUNTIF
=COUNTIF(IF(B11:B15<=G2;A11:A15);F2)
The red formula, passes to countif a range with only the rows that qualifies to the time criteria, so now you have a COUNTIF with 2 criteria...
Reply With Quote
  #4  
Old 03-24-2012, 11:21 AM
excelledsoftware excelledsoftware is offline formula help Windows 7 64bit formula help Office 2003
IT Specialist
formula help
 
Join Date: Jan 2012
Location: Utah
Posts: 455
excelledsoftware will become famous soon enough
Default

Quote:
Originally Posted by Catalin.B View Post
The bormula will be based on SUMPRODUCT, but you must upload a sample data to see the format used, especially for that military time, is it entered as text, or the column is formatted as date "hhmm" ?
My apologies. The format is in just a custom number with 0000 in order to show the 0 before. The number is calculated in a different table with vertical lookup. The number appears exactly as written and formula isnumber for the military time format = true.
Reply With Quote
  #5  
Old 03-24-2012, 11:44 AM
excelledsoftware excelledsoftware is offline formula help Windows 7 64bit formula help Office 2003
IT Specialist
formula help
 
Join Date: Jan 2012
Location: Utah
Posts: 455
excelledsoftware will become famous soon enough
Default

Quote:
Originally Posted by Catalin.B View Post
This formula assumes that you entered time in a cell formatted as text, and in G2 is the refference time, in format 16:00 PM
=SUMPRODUCT((TIME(LEFT(B11:B15;2);RIGHT(B11:B15;2) ;0)<=G2)*1)
Or, if you keep the same format in G2, simply: =SUMPRODUCT((B11:B15<=G2)*1)
To take into account the area, entered in F2: =SUMPRODUCT((TIME(LEFT(B11:B15;2);RIGHT(B11:B15;2) ;0)<=G2)*(A11:A15=F2))
Note: it is not an array formula, confirmed with CSE, so so it is not consuming so much resources. For another approach, using COUNTIF
=COUNTIF(IF(B11:B15<=G2;A11:A15);F2)
The red formula, passes to countif a range with only the rows that qualifies to the time criteria, so now you have a COUNTIF with 2 criteria...
I really like the countif approach. It does not quite work though. I changed the semi colons to commas and that got it to function but it is not counting just the cells with the desired before time it just counts the amount of times the area appears. I attached a sample so you can see the result. Again the numbers are all formatted as a number so a simplified sumproduct formula I guess could work but I really do like the countif formula.

Thanks so much for your help so far...
Attached Files
File Type: xls area counter test.xls (15.0 KB, 10 views)
Reply With Quote
  #6  
Old 03-24-2012, 01:00 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline formula help Windows 7 64bit formula help Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Perhaps
Code:
=SUMPRODUCT(($A$2:$A$6=G2)*($B$2:$B$6<=E2))
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #7  
Old 03-24-2012, 03:03 PM
excelledsoftware excelledsoftware is offline formula help Windows 7 64bit formula help Office 2003
IT Specialist
formula help
 
Join Date: Jan 2012
Location: Utah
Posts: 455
excelledsoftware will become famous soon enough
Default

Quote:
Originally Posted by Pecoflyer View Post
Perhaps
Code:
=SUMPRODUCT(($A$2:$A$6=G2)*($B$2:$B$6<=E2))
Last time I used this technique it slowed the spreadsheet way down. I just entered them all and it seems to be working fine with it. Thanks so much
Reply With Quote
  #8  
Old 03-25-2012, 01:23 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline formula help Windows 7 64bit formula help Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Did you use large ranges hat time ? Like A1:A65535 ? That would slow thing down
If necessary Dynamic ranges are best
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #9  
Old 03-25-2012, 08:49 AM
excelledsoftware excelledsoftware is offline formula help Windows 7 64bit formula help Office 2003
IT Specialist
formula help
 
Join Date: Jan 2012
Location: Utah
Posts: 455
excelledsoftware will become famous soon enough
Default

Quote:
Originally Posted by Pecoflyer View Post
Did you use large ranges hat time ? Like A1:A65535 ? That would slow thing down
If necessary Dynamic ranges are best
You know I think I did. It was shortly after I discovered the sum if formula which was much more effective for the application I was doing. But it is evident now that the sumproduct formula is exactly what I needed. Thanks again I will mark as solved.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula Help - Look and if and that Corca Excel 2 02-05-2012 09:18 AM
help with a look up or if formula beb1227 Excel 3 12-31-2011 01:08 PM
formula help help with formula? doczilla Excel 2 09-25-2011 04:14 PM
formula help Help with formula please. AndrewSE Excel 3 04-05-2011 08:50 PM
formula help Help with Formula Corca Excel 6 02-22-2010 09:40 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:51 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft