Count number of filtered rows according to distinct values and another criteria

**dino_ben** · 10-05-2020, 03:44 AM

Hello,
I've trying to solve a formula that I'm stuck on for a few days.
I have a table with the following 2 columns:
Column D - Date
Column J - Hours (Time format = 1:00)

What I want to achieve is to show the number of rows in the table (Not including filtered or hidden rows) that the time in column J is bigger then 0:00 and for it to only count a certain date once.

For example:

1-Sep 1:00
2-Sep 2:30
Will count as 2

1-Sep 1:00
2-Sep 0:00
Will count as 1

1-Sep 1:00
1-Sep 2:00
Will count as 1

If anyone has any idea how to solve this it would be much appreciated!
Thanks!!

**XLent** · 10-05-2020, 05:06 AM

it would help, I think, if you were to attach a sample file (refer banner at top of forum for 'how to' instructions)

it might be that the below Frequency Array would work for you but, be warned, this is not very efficient (and volatile)

Formula:

Please Login or Register  to view this content.

if you can store a visible flag in a spare column, e.g. =SUBTOTAL(2,D2) copied down, this would remove the volatile requirement (SUBTOTAL w/OFFSET) and simplify your calculation

**dino_ben** · 10-05-2020, 05:18 AM

I tried your formula but it didn't work for me.
Attaching an example file.
The calculation cell is at the total row at column D.

Thanks!

**XLent** · 10-05-2020, 06:29 AM

you have not quite copied correctly - in your formula, change

ROW($D$1:$D$91)
to
ROW($D$2:$D$91)

then re-confirm with CTRL + SHIFT + ENTER
(if modified correctly you should get result of 18 - which is correct, I believe)

Count number of filtered rows according to distinct values and another criteria

LinkBack

Thread Tools

Rate This Thread

Display

Count number of filtered rows according to distinct values and another criteria

Re: Count number of filtered rows according to distinct values and another criteria

Re: Count number of filtered rows according to distinct values and another criteria

Re: Count number of filtered rows according to distinct values and another criteria

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Count Distinct Values with Multiple Criteria

Count distinct values based on two criteria

Count Distinct Values With A Criteria In A Filtered List

[SOLVED] Count number of rows that match criteria and apply values relative to another criteria

Count distinct values that correspond to a criteria

[SOLVED] Excel 2007 : Count the number of Distinct Entries In an auto filtered Range .

Count distinct values with criteria

Bookmarks

Bookmarks

Posting Permissions