Countifs formula

[nods80]

hi I was hoping that someone could help with a countifs formula I am having problem with. I need to count how many times a bus takes more than 17 minutes to complete a circuit of a specific route on a specific day.

My data is organised like this.
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Length of time[/TD]
[/TR]
[TR]
[TD]12.03.2018AreaentryCar Park[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl63, width: 65"]00:38:28[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

the current formula i have that works fine but doesn't take into account the date in column A is:

=(COUNTIF(Monday!J:J,">=00:17"))

using this i need to separate my data on to tabs for each day of the week. I would like to save time by only using one tab.

I am trying something like this but can't get it to work

=countifs(A1,Monday!J:J,">=00:17")

Can anyone help?

Thanks

Nods

 

[wideboydixon]

The actual difficulty here is dealing with items in Column A that aren't actually dates (e.g. the header). You can use an array formula but it's going to run slow:

{=SUM(IF(IFERROR(WEEKDAY($A:$A,3),7)=0,IF($J:$J>=TIME(0,17,0),1,0)))}

Enter with Ctrl+Shift+Enter; don't try and put the {} in there yourself.

Change the highlighted 0 to 1 (Tuesday), 2 (Wednesday) etc.

If you could restrict the range to the actual values (rather than the whole column), there's a SUMPRODUCT that would work better.

WBD