CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
globalenemy
globalenemy

Reputation: 127

How to do FILTER on a set of durations in Google Sheets?

I'm trying to apply FILTER to a set of durations in a Google Sheets table. While searching for a solution online, I came across the TEXT function. But I can't figure out how to use it correctly.

Can you help me out?

For example I want to count all cells which have a duration below 40 minutes: enter image description here

I also would like to know how to do this with durations between let's say 30 and 50 minutes.

Upvotes: 1

Views: 586

Answers (2)

Aresvik
Aresvik

Reputation: 4630

Bewteen 30 and 50 mins using timevalue:

=filter(A:A,TIMEVALUE(A:A)>1/24/60*30,TIMEVALUE(A:A)<1/24/60*50)

enter image description here

enter image description here

Upvotes: 1

basic
basic

Reputation: 11968

If in range A1:B5 you have real time value, use COUNTIF:

=COUNTIF(A1:B5;"<" & 1/24/60*40)

enter image description here

if there is text, then add TIMEVALUE and ARRAYFORMULA:

=ArrayFormula(COUNTIF(TIMEVALUE(A1:B5);"<" & 1/24/60*40))

Upvotes: 2

Related Questions