#1
|
|||
|
|||
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.
|
#2
|
||||
|
||||
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 |
#3
|
|||
|
|||
Thank you very much for your answer, I have tried but I appear error. Attached screen. Thank you.
|
#4
|
||||
|
||||
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 |
#5
|
|||
|
|||
This message appears
|
#6
|
||||
|
||||
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 |
#7
|
|||
|
|||
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?
|
#8
|
||||
|
||||
Where in the cell is the text? Maybe remove the text and put it in a new adjacent column.
|
#9
|
|||
|
|||
|
#10
|
||||
|
||||
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. |
#11
|
|||
|
|||
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.
|
#12
|
||||
|
||||
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 |
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 |
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 |