Count Only Visible Days

[honkin]

Can the DAYS function be adapted to only count visible cells?

What I am trying to do is to divide the number of days between 2 dates into the number of selections I have. This tell me how many selections per day I have. Here is the formula I have:

=F5549/DAYS(A5524,A3)

This works alright when the sheet is unfiltered, but when I autofilter selections for certain criteria, it still counts the days from the first date in A3 to the last date in A5524. I would like it to only count the actual number of days showing from the first visible cell to the last visible cell. On some days there may be no selections, so I I don't want it to count simply from start date to finish date. I need it to differentiate when NO days are showing.

19/01/2017	Thursday	14:35
20/01/2017	Friday	19:15
20/01/2017	Friday	20:00
24/01/2017	Tuesday	14:40
25/01/2017	Wednesday	13:35
25/01/2017	Wednesday	15:50
31/01/2017	Tuesday	15:20
01/02/2017	Wednesday	18:30
02/02/2017	Thursday	21:00
03/02/2017	Friday	16:10
04/02/2017	Saturday	14:00
09/02/2017	Thursday	14:05
10/02/2017	Friday	18:15
10/02/2017	Friday	18:45
11/02/2017	Saturday	16:20

As you can see with this, there are only 12 actual days with entries, but if you counted from 19 Jan 2017 to 11 Feb 2017, you would get 24 days. I hope that clarifies it a little

Is there a way to adjust this to achieve what I am after?

cheers

 

[arthurbr]

=AGGREGATE(2,5,a3:a2254)

will only count visible rows so even when nothing is unvisible it still works
See what AGGREGATE can do

 

[honkin]

=AGGREGATE(2,5,a3:a2254)

will only count visible rows so even when nothing is unvisible it still works
See what AGGREGATE can do

Thanks very much for your reply, Arthur, but it doesn't appear to work with dates very well. The DAYS function shows there are 1519 days from 1 Jan 2017 to 28 Feb 2021. This answer is correct, as I checked it online. Using the Aggregate function, it gave the answer of 63,388, which is the actual number or rows of data. So it seems to be just tallying all visible rows, which is not what I need. Thanks again for your reply

 

[Tom.Jones]

Try this formula:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(a3:a70000,ROW(a3:a70000)-ROW(a3),0,1)))

Count ONLY visible cells.

 

[Anthony47]

In addition to what already posted by Tom.Jones, above...
It would be simple with a helper column; for example set in D3 this formula:
=SUBTOTAL(103,A3)*(COUNTIF($A2:$A$3,A3)=0)
Then copy D3 down to D6000

You can count the unique days in the visible rows using
=SUM(D3:D6000)

Bye

 

[honkin]

Try this formula:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(a3:a70000,ROW(a3:a70000)-ROW(a3),0,1)))

Count ONLY visible cells.

Cheers Tom. It's definitely heading in the right area. I was trying to work out how to use SUBTOTAL. What this does is count ALL the visible cells within the range, regardless of the dates. It is basically doing exactly the same as =SUBTOTAL(103,A3:A70000)

LTP Jumps.xlsx
	A	B	C
210	3/01/2017	Tuesday	14:30
211	3/01/2017	Tuesday	14:50
212	3/01/2017	Tuesday	14:50
213	3/01/2017	Tuesday	14:50
214	4/01/2017	Wednesday	12:55
215	4/01/2017	Wednesday	12:55
216	4/01/2017	Wednesday	12:55
217	4/01/2017	Wednesday	12:55
218	4/01/2017	Wednesday	12:55
Sheet1

So as an example, here are 9 rows, yet there are only 2 actual days displayed. The answer is 9, but it needs to be 2. It can't count duplicate days, but blowed if I can find a way to exclude them

 

[honkin]

In addition to what already posted by Tom.Jones, above...
It would be simple with a helper column; for example set in D3 this formula:
=SUBTOTAL(103,A3)*(COUNTIF($A2:$A$3,A3)=0)
Then copy D3 down to D6000

You can count the unique days in the visible rows using
=SUM(D3:D6000)

Bye

Thanks so much for your reply, Anthony. OK, so the idea is that gives a 1 every time the date changes, yeah? Then we tally the visible 1s to check for the number of days. Again, it seems to fall over when the data is autofiltered.

LTP Jumps.xlsx
	A	B	C
62651	18/02/2021	Thursday	0
62652	18/02/2021	Thursday	0
62653	18/02/2021	Thursday	0
62654	18/02/2021	Thursday	0
62655	18/02/2021	Thursday	0
62656	18/02/2021	Thursday	0
62696	19/02/2021	Friday	0
62697	19/02/2021	Friday	0
62698	19/02/2021	Friday	0
62699	19/02/2021	Friday	0
62700	19/02/2021	Friday	0
63065	23/02/2021	Tuesday	0
63066	23/02/2021	Tuesday	0
63067	23/02/2021	Tuesday	0
63068	23/02/2021	Tuesday	0
63104	24/02/2021	Wednesday	0
63105	24/02/2021	Wednesday	0
63106	24/02/2021	Wednesday	0
63107	24/02/2021	Wednesday	0
63364	28/02/2021	Sunday	0
63365	28/02/2021	Sunday	0
63366	28/02/2021	Sunday	0
Sheet1

Cell Formulas
Range	Formula
C63364:C63366,C63104:C63107,C63065:C63068,C62696:C62700,C62651:C62656	C62651	=SUBTOTAL(103,A62651)*(COUNTIF($A$3:$A62650,A62651)=0)

Here you can see it is 18, 19, 23, 24 & 28 Jan, yet all the entries are 0s. It works fine when the sheet is left unfiltered, but the idea of this is to be able to filter for certain criteria and see how many selections there are per day. If unique days are not displayed, that calculation cannot take place.

Any thoughts on why it doesn't recalculate based on only visible data?

cheers

 

[arthurbr]

Thanks very much for your reply, Arthur, but it doesn't appear to work with dates very well. The DAYS function shows there are 1519 days from 1 Jan 2017 to 28 Feb 2021. This answer is correct, as I checked it online. Using the Aggregate function, it gave the answer of 63,388, which is the actual number or rows of data. So it seems to be just tallying all visible rows, which is not what I need. Thanks again for your reply

Yep I used the COUNT switch instead of the COUNTA one

 =AGGREGATE(3,5,a3:a2254)

 

[Anthony47]

Do you mean your data spans from A3 to A63366?
I don't know if Subtotal(103 exists on Excel for Mac; try Subtotal(3:

=SUBTOTAL(3,A3)*(COUNTIF($A2:$A$3,A3)=0)

You should see a 1 on all the visible cells, if the date is not duplicated.

If it fails then I think you should share your worksheet with your real data

Bye

PS: don't miss arturbr message, above
And double check that your calculation mode is set to Automatic and not Manual

 

[Marcelo Branco]

This formula is not correct. See post #25

See if this example helps

Before filter

Pasta1
	A	B	C	D	E	F
1						Unique Days
2	Date	Weekday	Time	Criteria		7
3	19/01/2017	Thursday	14:35:00	Yes
4	19/01/2017	Thursday	15:00:01	No
5	19/01/2017	Thursday	15:01:01	No
6	19/01/2017	Thursday	15:02:01	No
7	19/01/2017	Thursday	15:03:01	No
8	19/01/2017	Thursday	15:04:01	No
9	19/01/2017	Thursday	15:05:01	No
10	20/01/2017	Friday	19:15:00	Yes
11	20/01/2017	Friday	20:00:00	Yes
12	21/01/2017	Saturday	20:01:00	No
13	21/01/2017	Saturday	20:02:00	No
14	22/01/2017	Sunday	15:01:01	No
15	22/01/2017	Sunday	15:02:01	No
16	23/01/2017	Monday	15:03:01	No
17	24/01/2017	Tuesday	14:40:00	Yes
18	24/01/2017	Tuesday	15:02:01	No
19	24/01/2017	Tuesday	15:03:01	No
20	25/01/2017	Wednesday	13:35:00	Yes
21	25/01/2017	Wednesday	14:00:00	No
22	25/01/2017	Wednesday	15:50:00	Yes
23
Plan1

Cell Formulas
Range	Formula
F2	F2	=SUM(IFERROR(IF(FREQUENCY(A3:A6000,A3:A6000),IF(SUBTOTAL(3,OFFSET(A3:A6000,ROW(A3:A6000)-ROW(A3),0,1)),1)),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

After filter (Criteria =Yes)

Pasta1
	A	B	C	D	E	F
1						Unique Days
2	Date	Weekday	Time	Criteria		4
3	19/01/2017	Thursday	14:35:00	Yes
10	20/01/2017	Friday	19:15:00	Yes
11	20/01/2017	Friday	20:00:00	Yes
17	24/01/2017	Tuesday	14:40:00	Yes
20	25/01/2017	Wednesday	13:35:00	Yes
22	25/01/2017	Wednesday	15:50:00	Yes
23
Plan1

Cell Formulas
Range	Formula
F2	F2	=SUM(IFERROR(IF(FREQUENCY(A3:A6000,A3:A6000),IF(SUBTOTAL(3,OFFSET(A3:A6000,ROW(A3:A6000)-ROW(A3),0,1)),1)),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Array formula in F2 (must be confirmed with Ctrl+Shift+Enter)

M.