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.

A	J
Date	Length of time
12.03.2018AreaentryCar Park	00:38:28 <tbody> </tbody>

<tbody>
</tbody>

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