Excel - ROW Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.