Trying to get Totals per Time and Date

**nigo75** · 05-27-2013, 10:51 PM

Hi All,
Im trying to get a formula to work for my count of personnel per dayshift (Z1:Z6 to AA1:AA6) and per night shift (Z7:Z11 to AA7:AA11) (both seperated in totals) for each Vendor by using column (Q,R) and Vendor (L)
My first try highlighted in Orange was basic but didnt include/exclude certain times (before or after) or include vendor.

Now to be a real pain is there a way I can exclude a total count if it creeps before or after the set shift times by say 1/2 hour - 1 hour either way. (Would it be excluded by a percentage add in of before/after shifts????)

Any Help would be much appreciated

**FDibbins** · 05-28-2013, 12:46 AM

Hi and welcome to the forum

give this a try...
=COUNTIFS($Q$2:$Q$13,">="&Z1,$R$2:$R$13,"<="&AA1)

THis could be added to, to included a vendor, and the times in your table could be adjusted in the formula to include/exclude a specified amount of time. Just remember that excel considers time to be a fraction of a day, so 6 hours is actually 6/24 = 0.25

**nigo75** · 05-28-2013, 03:51 AM

Thanks FDibbins works exactly how I asked for it,
the only problem is I forgot to make a reference if one Vendor does 3 jobs within the given time period the count will be 3 whereas the count will be one as they cannot do all jobs at once. ive tried a few different add ons to this formula but nothing has worked............??

**FDibbins** · 05-29-2013, 12:35 AM

I added a helper column in X (you can put it anywhere, and you can hide it if you want) a,d used this, copied down...

=IF(COUNTIF($L$2:L2,L2)>1,"",1)

Then change my 1st formula to...
=COUNTIFS($Q$2:$Q$13,">="&Z1,$R$2:$R$13,"<="&AA1,$X$2:$X$13,1)