Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-18-2017, 03:48 AM
sprit36 sprit36 is offline Count people by time Windows 10 Count people by time Office 2010 64bit
Novice
Count people by time
 
Join Date: Jul 2017
Posts: 9
sprit36 is on a distinguished road
Default Count people by time

I need a formula that counts people available by time slot, the result would be on the other sheet, there are cells that have two time slots. I attach an example. Thank you.
Attached Files
File Type: xls Count.xls (35.0 KB, 24 views)
Reply With Quote
  #2  
Old 07-18-2017, 05:38 AM
NBVC's Avatar
NBVC NBVC is offline Count people by time Windows 10 Count people by time Office 2013
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 215
NBVC will become famous soon enoughNBVC will become famous soon enough
Default

Of course, it would be easier if you had all your start/end times in separate cells... but you can try this in B3:

=SUMPRODUCT((MID(Sheet1!B$4:B$24,1,5)+0<=$A3)*(MID (Sheet1!B$4:B$24,FIND("-",Sheet1!B$4:B$24)+1,5)+0>=$A3))+SUMPRODUCT(( IFERROR(MID(Sheet1!B$4:B$24,13,5)+0,0)<=$A3)*( IFERROR(MID(Sheet1!B$4:B$24,19,5)+0,0)>=$A3))

you must confirm this formula with CTRL+SHIFT+ENTER not just ENTER, then copy to the right and down
Reply With Quote
  #3  
Old 07-19-2017, 03:08 AM
sprit36 sprit36 is offline Count people by time Windows 10 Count people by time Office 2010 64bit
Novice
Count people by time
 
Join Date: Jul 2017
Posts: 9
sprit36 is on a distinguished road
Default

Thank you very much for your answer, I have tried but I appear error. Attached screen. Thank you.
Reply With Quote
  #4  
Old 07-19-2017, 04:49 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Count people by time Windows 7 64bit Count people by time 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

Which error?
__________________
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
  #5  
Old 07-19-2017, 05:26 AM
sprit36 sprit36 is offline Count people by time Windows 10 Count people by time Office 2010 64bit
Novice
Count people by time
 
Join Date: Jul 2017
Posts: 9
sprit36 is on a distinguished road
Default

This message appears
Attached Images
File Type: png Error2.png (7.4 KB, 28 views)
Reply With Quote
  #6  
Old 07-19-2017, 05:43 AM
NBVC's Avatar
NBVC NBVC is offline Count people by time Windows 10 Count people by time Office 2013
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 215
NBVC will become famous soon enoughNBVC will become famous soon enough
Default

If you are using a European version of Excel, you may need to change all the commas to semi colons as parameter separators.

Here is your sheet with the formulas installed
Attached Files
File Type: xls Copy of Count.xls (45.5 KB, 14 views)
Reply With Quote
  #7  
Old 07-20-2017, 12:26 AM
sprit36 sprit36 is offline Count people by time Windows 10 Count people by time Office 2010 64bit
Novice
Count people by time
 
Join Date: Jul 2017
Posts: 9
sprit36 is on a distinguished road
Default

Thanks a lot for your help, it works perfectly, the only problem I find is that if there is any text annotation in some cell, for example holidays, it appears error: #VALUE! Is there any way to omit the texts?
Reply With Quote
  #8  
Old 07-20-2017, 10:53 AM
NBVC's Avatar
NBVC NBVC is offline Count people by time Windows 10 Count people by time Office 2013
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 215
NBVC will become famous soon enoughNBVC will become famous soon enough
Default

Where in the cell is the text? Maybe remove the text and put it in a new adjacent column.
Reply With Quote
  #9  
Old 07-20-2017, 11:33 AM
NoSparks NoSparks is offline Count people by time Windows 7 64bit Count people by time Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

@ NBVC

https://www.excelforum.com/excel-pro...ml#post4656389
Reply With Quote
  #10  
Old 07-20-2017, 11:39 AM
NBVC's Avatar
NBVC NBVC is offline Count people by time Windows 10 Count people by time Office 2013
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 215
NBVC will become famous soon enoughNBVC will become famous soon enough
Default

Thanks NoSparks.

sprit36, please read the article in the this link. Most people, like me, get upset knowing that you have people helping you on the same topic in several places without us knowing that. We can be helping others while you are getting your answer elsewhere.
Reply With Quote
  #11  
Old 07-21-2017, 12:03 AM
sprit36 sprit36 is offline Count people by time Windows 10 Count people by time Office 2010 64bit
Novice
Count people by time
 
Join Date: Jul 2017
Posts: 9
sprit36 is on a distinguished road
Default

I regret if he was angry, the moment I published this message I had no response from the other forum and did not know if I would have an answer. The reason for publishing it was because it urgently needed to solve the problem. You are absolutely right, I understand and apologize. It was not my intention to duplicate the subject, and its solution was what I was looking for, in the other forum I get a different solution to yours that is also valid, but with its solution I avoid a step since it does it directly. I am very grateful for your help also to the people of the other forum who have also helped me. I apologize and it will not happen again. Thank you.
Reply With Quote
  #12  
Old 07-21-2017, 09:30 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Count people by time Windows 7 64bit Count people by time 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

We do understand your need for an urgent solution, and appreciate you took some time to explain what happened.
Again, cross posting is not forbidden, we just ask ( as do other forums) that you add a link to your cross posts.
My experience tells me that the more you cross post a question, the less chance you have getting an answer, BTA that is your choice.
Also looking for urgent solutions on free forums is sometimes counter-productive as there is no guarantee that you will get a fast answer. But, of course, you can always try .
We will be glad to help you in the future, np ( and I think NBVC too, he's a great guy and a magician at XL)
Cheers
__________________
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
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple Timesheets - id'ing two people working on the same machine at the same time. Will Excel 1 09-28-2016 09:12 AM
Count people by time Getting the Count formula to count all rows Jennifer Murphy Word Tables 11 08-23-2016 09:37 PM
Count the common time period (month) between two date period of time Barni Excel 6 08-15-2014 07:52 AM
How to address a letter to 10,000 different people at once. Bit of a Trip Word 0 08-13-2010 08:44 AM
Meeting Invites to too many people daisydlx Outlook 0 09-10-2009 12:21 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:37 AM.


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