CODEWITHSUNDEEP
excelfunctionexcel-formula
Saud
Saud

Reputation: 490

Error in COUNTIFS function

I want to count how many vehicles are delayed more than 4 min on a given day according to a given departure (let's assume from 00:00 to 05:00).

This is a sample of the data:

A B C D
1 Line Day Departure Delayed (sec)
2 11    Weekday 02:30:00    120
3 11    Weekday 03:40:00    500
4 22    Weekday 01:45:00    10
5 44    Weekday 06:44:00    1000
6 55    Weekday 04:35:00    145
7 111   Saturday    14:40:00    450
8 111   Saturday    04:20:00    300
9 111   Saturday    20:20:00    220
10 111  Saturday    07:00:00    125
11 333  Sunday  09:15:00    700

I used a "TÆL.HVISER" function (Danish) or COUNT.IFS function to count the vehicles:

=TÆL.HVISER(A2:A11;"11";B2:B11;"Weekday";C2:C11;00:00:00>C2:C11>05:00:00;D2:D11;">240")

But it is not working. When I break this restriction into four restrictions, the individual restrictions are working but when I combine them it's not working.

Upvotes: 0

Views: 87

Answers (2)

Pavel_V
Pavel_V

Reputation: 1230

It is the part 

00:00:00>C2:C11>05:00:00

if you change it to two criteria like this

C2:C11;">00:00:00";C2:C11;"<05:00:00"

it will work. Here is the full formula:

=COUNTIFS(A2:A11;"11";B2:B11;"Weekday";C2:C11;">00:00:00";C2:C11;"<05:00:00";D2:D11;">240")

Upvotes: 2

user4039065
user4039065

Reputation:

I've laid out your data according to how I read your sample formula.

    Time Delays

The EN-US formula in G4 is,

=COUNTIFS($A$2:$A$11, G$3, $B$2:$B$11, $F4, $C$2:$C$11, ">="&TIME(0, 0, 0), $C$2:$C$11, "<="&TIME(5, 0, 0), $D$2:$D$11, ">="&240)

Fill both right and down. I've use the TIME function so that a) real times could be referenced and b) it makes it easier to set to new values.

TÆL.HVISER, funktionen

Funktionen TID

Upvotes: 2

Related Questions