Countifs with different restrictions

[joppert87]

Hi all,
hope you can help me with an issue i'm trying to solve in Excel 2013:

Data:
A (name) B (Target time) C (actual time)
1 John 16:30:00 16:35:00
2 Lenny 16:30:00 15:10:00
3 John 14:30:00 16:50:00
4 Lisa 16:00:00 16:50:00
5 John 15:30:00 17:00:00
6 John 16:30:00 13:00:00
.....
.....

What I would like to calculate
1) How many times John is on time
2) How many times John is to late
3) How many times John is to late but between a set time frame:
3A) Up to 30 minutes late
3B) up to 60 minutes late

For 1) and 2) I solved the problem with the following formulas:
=COUNTIFS(A1:A9;"John";C1:C9;"<=" &B1:B9)
and
=COUNTIFS(A1:A9;"John";C1:C9;">" &B1:B9)

But now I'm stuck with problem 3.


I could solve it by using a new column to see if the end time is between a set value and count those results,

=IF((AND(C1-B1>TIME(0;0;0);C1-B1<=TIME(0;30;0)));1;IF((AND(C1-B1>TIME(0;30;0);C1-B1<=TIME(0;60;0)));2;IF((AND(C1-B1>TIME(0;60;0);C1-B1<=TIME(1;0;0)));3;"on time")))

but I hope there is a direct way to do this with just one formula...



Any help will be highly appreciated!! thnx in advance,

Sincerely,
Joppert

 

[Claus Busch]

Hi,

Am Thu, 29 Nov 2012 11:48:05 -0800 (PST) schrieb (e-mail address removed):

A (name) B (Target time) C (actual time)
1 John 16:30:00 16:35:00
2 Lenny 16:30:00 15:10:00
3 John 14:30:00 16:50:00
4 Lisa 16:00:00 16:50:00
5 John 15:30:00 17:00:00
6 John 16:30:00 13:00:00
....
....

3) How many times John is to late but between a set time frame:
3A) Up to 30 minutes late
3B) up to 60 minutes late

up to 30 minutes late:
=SUMPRODUCT(--(A1:A10="John"),--(C1:C10<=B1:B10+TIME(,30,)),--(C1:C10>B1:B10))
more than 30 minutes up to 60 minutes:
=SUMPRODUCT(--(A1:A10="John"),--(C1:C10<=B1:B10+TIME(1,,)),--(C1:C10>B1:B10+TIME(,30,)))
more than 60 minutes late:
=SUMPRODUCT(--(A1:A10="John"),--(C1:C10>B1:B10+TIME(1,,)))


Regards
Claus Busch