Working With Time in Filtered Table

[Brendan Cain]

Hello,

I have a table of phone calls that includes a duration in (h):mm:ss format. At the top of the table is a dashboard that shows total calls, unique calls, etc... I also need to show calls that fall below 30 seconds, between 30 seconds and 59 seconds, and above 1 minute. This needs to change appropriately if a filter is applied (e.g., a specific number or source). Here is what I have so far for calls below 30 seconds:

=SUMPRODUCT(SUBTOTAL(103,OFFSET(A1,ROW(E19:E5018)-ROW(A1),0)),--(E19:E5019<"00:30"))

It returns the #VALUE and no amount of tweaking on my part has been able to get a correct return that changes when filtered. Any and all help is appreciated!

 

[Chihiro]

Welcome to the forum.

Have a read of the links.
https://chandoo.org/wp/2010/05/11/exclude-hidden-rows-from-totals/
https://chandoo.org/wp/2014/07/23/formula-forensics-no-037-how-to-count-and-sum-filtered-tables/

Unless there's very compelling reason to not use helper column. 1st method is easier to maintain and probably more efficient.

If that doesn't help you solve your issue, upload sample workbook with enough data to demonstrate your issue. Clearly indication which columns contain what info and where results should be shown (manually fill them out with correct answer).

 

[Brendan Cain]

Welcome to the forum.

Have a read of the links.
https://chandoo.org/wp/2010/05/11/exclude-hidden-rows-from-totals/
https://chandoo.org/wp/2014/07/23/formula-forensics-no-037-how-to-count-and-sum-filtered-tables/

Unless there's very compelling reason to not use helper column. 1st method is easier to maintain and probably more efficient.

If that doesn't help you solve your issue, upload sample workbook with enough data to demonstrate your issue. Clearly indication which columns contain what info and where results should be shown (manually fill them out with correct answer).

Hi Chihiro,

Thank you for responding. I have attached a sample workbook. Basically, I need to count the calls below 30 seconds. The time duration is in column D (it was column E, but I removed a column for privacy purposes). I need the calculation in L13 to change if the data is filtered on any row. I appreciate any help!

 

[Chihiro]

See attached. L13 has method using COUNTIFS with helper column.

Just as sample added formula in L14 using SUMPRODUCT method.
=SUMPRODUCT(SUBTOTAL(102,OFFSET(D19,ROW(A19:A53)-ROW(A19),0)),(D19:D53<TIME(0,0,30))+0)

To explain your issue, there were 2 mistakes in the formula.

1. -ROW(A1) should have been first row of your data. So -ROW(A19).

2. "00:30" is equivalent of 30 min. 30 sec would be "00:00:30". I prefer to use Time() function to denote time rather than string.

 

[Brendan Cain]

See attached. L13 has method using COUNTIFS with helper column.

Just as sample added formula in L14 using SUMPRODUCT method.
=SUMPRODUCT(SUBTOTAL(102,OFFSET(D19,ROW(A19:A53)-ROW(A19),0)),(D19:D53<TIME(0,0,30))+0)

To explain your issue, there were 2 mistakes in the formula.

1. -ROW(A1) should have been first row of your data. So -ROW(A19).

2. "00:30" is equivalent of 30 min. 30 sec would be "00:00:30". I prefer to use Time() function to denote time rather than string.

Awesome! Thank you so much! This works wonderfully!

 

[Brendan Cain]

See attached. L13 has method using COUNTIFS with helper column.

Just as sample added formula in L14 using SUMPRODUCT method.
=SUMPRODUCT(SUBTOTAL(102,OFFSET(D19,ROW(A19:A53)-ROW(A19),0)),(D19:D53<TIME(0,0,30))+0)

To explain your issue, there were 2 mistakes in the formula.

1. -ROW(A1) should have been first row of your data. So -ROW(A19).

2. "00:30" is equivalent of 30 min. 30 sec would be "00:00:30". I prefer to use Time() function to denote time rather than string.

Hi Chihiro,

I'm helping you can help me with one final thing. If I want to use the formula you provided for times between 30 and 59 seconds, how would I do this?

Thanks again!

 

[Chihiro]

For COUNTIFS
=COUNTIFS($D$19:$D$53,">="&TIME(0,0,30),$D$19:$D$53,"<"&TIME(0,0,60),$O$19:$O$53,1)

For SUMPRODUCT
=SUMPRODUCT(SUBTOTAL(102,OFFSET(D19,ROW(A19:A53)-ROW(A19),0)),(D19:D53>=TIME(0,0,30))*(D19:D53<TIME(0,0,60))+0)

FYI - You don't need +0 since * operation will coerce numeric. But for sake of consistency I left it in.

 

[Brendan Cain]

For COUNTIFS
=COUNTIFS($D$19:$D$53,">="&TIME(0,0,30),$D$19:$D$53,"<"&TIME(0,0,60),$O$19:$O$53,1)

For SUMPRODUCT
=SUMPRODUCT(SUBTOTAL(102,OFFSET(D19,ROW(A19:A53)-ROW(A19),0)),(D19:D53>=TIME(0,0,30))*(D19:D53<TIME(0,0,60))+0)

FYI - You don't need +0 since * operation will coerce numeric. But for sake of consistency I left it in.

Thank you!