Hi,
Below is the excel input.
FROM_DATE|VALUE1|TIME_DIFFERENCE|TO_DATE|VALUE2
2017-11-28 12:39:00|PASS|00:02:27|2017-11-28 12:41:27|101
2017-11-28 12:39:00|PASS|00:00:02|2017-11-28 12:39:02|509
2017-11-28 12:39:00|FAIL|05:57:01|2017-11-28 18:36:01|999
2017-11-28 12:39:00|PASS|00:00:09|2017-11-28 12:39:09|101
2017-11-28 12:39:02|PASS|00:05:45|2017-11-28 12:44:47|700
In above excel TIME_DIFFERENCE is calculated as below:
I want the output for below cases
CASES:
Total count of VALUE1=PASS having TIME_DIFFERENCE more than 2 minutes and having VALUE2=101
Total count of VALUE1=PASS having TIME_DIFFERENCE less than 2 minutes and 30 sec and having VALUE2=101
I'm trying with below formula but it does not output the data as required.
=COUNTIFS(B1:B5,"PASS",C1:C5,"MINUTE(C1:C5)>2")
I need help to resolve the above cases
Bookmarks