![]() |
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 |
Countifs with different restrictions
|
All times are GMT +1. The time now is 02:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com