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.
