ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Countifs with different restrictions (https://www.excelbanter.com/excel-worksheet-functions/447757-countifs-different-restrictions.html)

[email protected]

Countifs with different restrictions
 
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-B1TIME(0;0;0);C1-B1<=TIME(0;30;0)));1;IF((AND(C1-B1TIME(0;30;0);C1-B1<=TIME(0;60;0)));2;IF((AND(C1-B1TIME(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

Countifs with different restrictions
 
Hi,

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

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


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2


All times are GMT +1. The time now is 02:13 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com