**Question description:**

*This user has given permission to use the problem statement for this blog.*

Hi, I need a formula to work out how many values are less than a certain time value, simple "countif" do not work as it is a filtered spreadsheet.
Here's the formula that I have got so far:
SUMPRODUCT(SUBTOTAL(3,OFFSET(Current_Month!N$2:N$10000,ROW(Current_Month!N$2:N$10000)-MIN(ROW(Current_Month!N$2:N$10000)),,1)),--(Current_Month!N$2:N$10000>"24:0:0")

Solved by K. L. in 30 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
07/08/2018 - 02:05

hello..??

User
07/08/2018 - 02:06

Hi

Excelchat Expert
07/08/2018 - 02:06

how can i help you today..??

User
07/08/2018 - 02:07

I need a formula to work out how many values are less than a certain time value, simple "countif" do not work as it is a filtered spreadsheet. Here's the formula that I have got so far: SUMPRODUCT(SUBTOTAL(3,OFFSET(Current_Month!N$2:N$10000,ROW(Current_Month!N$2:N$10000)-MIN(ROW(Current_Month!N$2:N$10000)),,1)),--(Current_Month!N$2:N$10000>"24:0:0")

Excelchat Expert
07/08/2018 - 02:07

can you upload your file in here..??

User
07/08/2018 - 02:08

Sorry I am not able to on this pc

Excelchat Expert
07/08/2018 - 02:08

can you give me a example on the preview window..??

User
07/08/2018 - 02:08

How can I count how many values are less than 24 hrs

User
07/08/2018 - 02:09

and the values should change with changing the filter

Excelchat Expert
07/08/2018 - 02:09

what is cell format you want to count..?

User
07/08/2018 - 02:09

the cell format is...

User
07/08/2018 - 02:09

time

User
07/08/2018 - 02:09

00:00:00

Excelchat Expert
07/08/2018 - 02:14

hold on..

Excelchat Expert
07/08/2018 - 02:21

you want to count less than 24 hours.. right..??

User
07/08/2018 - 02:23

got disconnected

Excelchat Expert
07/08/2018 - 02:23

no problem..

User
07/08/2018 - 02:23

sorry

Excelchat Expert
07/08/2018 - 02:23

you want to count less than 24 hours only right..??

User
07/08/2018 - 02:23

yes

User
07/08/2018 - 02:23

but in a filtered lisy

User
07/08/2018 - 02:24

list*

Excelchat Expert
07/08/2018 - 02:24

ok..

Excelchat Expert
07/08/2018 - 02:24

hold on..

Excelchat Expert
07/08/2018 - 02:32

hi..e

Excelchat Expert
07/08/2018 - 02:33

I got the formula you want..

Excelchat Expert
07/08/2018 - 02:33

[Uploaded an Excel file]

Excelchat Expert
07/08/2018 - 02:33

can you see

Excelchat Expert
07/08/2018 - 02:33

please let me know what you think..

Excelchat Expert
07/08/2018 - 02:34

If chat windows is not working

Excelchat Expert
07/08/2018 - 02:34

you can type in the preview window..

Excelchat Expert
07/08/2018 - 02:34

If you open the file..

Excelchat Expert
07/08/2018 - 02:35

I have time listed on column C..

Excelchat Expert
07/08/2018 - 02:35

C14 is the formula..

Excelchat Expert
07/08/2018 - 02:35

=SUMPRODUCT(SUBTOTAL(3,OFFSET(C2:C11,ROW(C2:C11)-MIN(ROW(C2:C11)),,1))*(C2:C11<1))

Excelchat Expert
07/08/2018 - 02:35

If you filter..

Excelchat Expert
07/08/2018 - 02:36

it will only count filtered cells which are less than 24 hours..

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*