Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countifs with different restrictions
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Restrictions in format | Excel Discussion (Misc queries) | |||
RESTRICTIONS | Excel Discussion (Misc queries) | |||
Countifs to Sumproduct for 2003 - was Simple Countifs.. from Fr | Excel Worksheet Functions | |||
How to do look up with restrictions | Excel Discussion (Misc queries) | |||
get_Range Restrictions.. | Excel Programming |