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

 

[honkin]

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

Data spans from A3 to A6390, Anthony. Automatic is always set and arturbr's additional post made no difference. It still merely counts the number of rows and does NOT differentiate between new days. By the way, Subtotal 103 does work on a Mac, as have it working for me perfectly in another sheet.

cheers

 

[honkin]

Yep I used the COUNT switch instead of the COUNTA one

 =AGGREGATE(3,5,a3:a2254)

Thanks again for your reply. It made no difference, though. It simply counts the numbers or visible rows

cheers

 

[honkin]

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.

Cheers Marcelo and thanks so much for the reply. Much better not to have to use a helper column. It took some working out how to enter it, though, as MacOS has some funny quirks with Mission Control and keyboard shortcuts. Once I found out how to disable that, entering the array formula was fine. The call range is A3:A63390, so amended that. The date range is 1 Jan 2017 to 28 Feb 2021. The actual number of days in that range is 1519; 1285 was the answer which presented, but it will be quite possible not all dates in that span have an entry, so 1285 seems pretty right. It just means that in a little over 4 years, there were 234 days without a selection.

I needed to divide this answer into the total number of selections, which is a dynamic amount in cell F63396, so added =F63396/ before the word SUM. it seems to work fine.

Thanks very much for this answer. It seems ideal. Thanks also to everyone who contributed. It's often more difficult on Excel for Mac, but eventually the answer emerged.

cheers

 

[Marcelo Branco]

You're welcome. Glad to help.

M,

 

[honkin]

You're welcome. Glad to help.

M,

ha! It does appear I may have spoken too soon, Marcelo, as on filtering, it is not in any way accurate.

So one of the sheets I wan to use it in has just over 122,000 rows, being for all of 2019. When unfiltered, it works perfectly and I had assumed it was working perfectly as the number was changing as I filtered. The downside is it is simply not counting all rows for unique dates.

So I just filter for any selection which was <2. This took the number of rows down from 122,337 to 1706 and the number of unique days shows as 48. It is, however, miles off. In January 2019 alone there are 30 different days showing, with only January 24 not appearing.

I filtered even more extremely, for anything <1.2. There are 50 selections (rows) and 48 different days, yet the answer shows as 3

Here is just a small screen grab of that. 3 is the answer, yet, even only going back to May 2018, you. can see there are more than 3

Any ideas why it would fall over like that on filtering? Mine is Excel for Mac, so am wondering if it is some quirk there.

Here is the formula I have

{=SUM(IFERROR(IF(FREQUENCY(A3:A122337,A3:A122337),IF(SUBTOTAL(3,OFFSET(A3:A122337,ROW(A3:A122337)-ROW(A3),0,1)),1)),0))}

It is entered in the correct way, CTRL + SHIFT + ENTER, but for whatever reason, it doesn't count correctly when the data is filtered.

 

[Anthony47]

For sure Marcelo will have a fix for this strange behaviour.

In the meantime let me offer the a User Defined Function (UDF):
1) copy in a standard module of your vba project the following code:

Function CountDays(ByRef myStAdr As Range, Optional Dummy As Range) As Long
'Use =CountDays(TheStartOfTheDates[, optional ARangeThatForcesRecalculation])
'
Dim myC As Range, myDic As Object, dCnt As Long, myRan As Range, myTim As Single
'
'myTim = Timer
LastRow = myStAdr.Cells(1, 1).End(xlDown).Row
If LastRow < Application.Rows.Count Then
    Set myDic = CreateObject("Scripting.Dictionary")
    For Each myC In myStAdr.Resize(LastRow - myStAdr.Cells(1, 1).Row + 1)
        If myC.EntireRow.Hidden = False Then
            myk = myC.Value
            If Not myDic.exists(myk) Then
                myDic.Add (myk), 1
                dCnt = dCnt + 1
            End If
        End If
    Next myC
End If
CountDays = dCnt
'Debug.Print Timer - myTim
End Function

2) Now go to your worksheet, and you may calcualte the unique days in the filtered column using this formula:

=CountDays(A3)

This supposes that the filterd dates started from A3 (your case, I seem)

This is demontrated in the worksheet that can be downloaded from here: Demo_Filtered-Dates.xlsm
In Foglio1 column A there are some 150k random dates, all ranging from Jan-1-2021 to dec-31-2021; in column B random numbers from 1 to 50 are inserted.
Cell H1 calculate the unique days in the filtered area using the UDF

Autofileter should force recalculation, so the result will recalculate automatically.
However the udf has on optional parameter that can force recalculation if it changes.

A possible use of this parametre is again demonstrated on the sample workbook: if some dates are appended to the filtered area (without refiltering the column) the new value is automatically recalculated because I used cell E1 for the optional parameter, and E1 calculates with the formula =SUBTOTAL(9,A1:A200000)

The demo workbook also contains sheets ScrSh that is only used for checking that the UDF returns the correct value:
-from Foglio1 the button "Copy toScrSh" copies the filtered data from Foglio1 to ScrSh
-column H in ScrSh contains all the days from Jan-1 to Dec-31-2021
-in column I the formula report 1 if that date in in column A
-cell I1 sums the available days
-cell O1 is compiled with the result of the UDF and can be visually checked against value calculated in I1

Of course an UDF is slower that an efficient formula, so this is more an exercise than a solution…

Bye

 

[Marcelo Branco]

I'm not sure how to help anymore because:
1. I don't use Excel for MAC
2. I don't have such a large amount of data to do tests
3. The formula worked perfectly for me (with a small sample of data)

By the way, I didn't quite understand what you meant by filtering <2 or <1.2

I hope you can find a solution.

M.

 

[Marcelo Branco]

Still trying to help - questions
Could you show us the formula you are using?
Are you sure that all the dates in column A are numbers instead of text that looks like dates?

M.

 

[honkin]

I'm not sure how to help anymore because:
1. I don't use Excel for MAC
2. I don't have such a large amount of data to do tests
3. The formula worked perfectly for me (with a small sample of data)

By the way, I didn't quite understand what you meant by filtering <2 or <1.2

I hope you can find a solution.

M.

hi Marcelo

The <2 or 1.2 was just filtering in other columns. There is a column which shows the price of the home team, so I was filtering for any which were odds on. There are many columns used for autofiltering, so that is why I need it to be able to crunch through a large volume

Thanks so much for getting back to me

 

[honkin]

Still trying to help - questions
Could you show us the formula you are using?
Are you sure that all the dates in column A are numbers instead of text that looks like dates?

M.

This is the formula Marcelo. I basically just copied the formula you gave and altered the final row number

=SUM(IFERROR(IF(FREQUENCY(A3:A122337,A3:A122337),IF(SUBTOTAL(3,OFFSET(A3:A122337,ROW(A3:A122337)-ROW(A3),0,1)),1)),0))

Yes, all the entries in column A are the same. They are formatted as Date. I even tried formatting them all as Custom and dd/mm/yyyy, but it made no difference

cheers