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

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

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!!

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

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

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

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!

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

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)

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1