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]

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

cheers Anthony and thanks for the reply

I downloaded the demo workbook you linked, but have some issues with it, as none of it functions as you have indicated. The main issue is the array formula in M1, which simply does not work. I mean, it changes as I filter column B for different numbers, but it is not in any way accurate in counting the unique days. I just filtered for the number 3 and M1, which is the array formula, shows 6, where there are many hundreds of unique days showing.

The formula for counting the unique days, which you indicated was =CountDays(A3) actually does nothing when I input it in my sheet, after entering the VBA code into a new module. In your worksheet, though, you actually have that code as =CountDays(A2,E1) and you have it entered as an array formula in H1, rather than a normal formula. In your worksheet, the value is #VALUE! at all times, regardless of the filtering.

The array formula in M1 shows 365 as the result when nothing is filtered, which certainly seems right, seeing as it is the entire year, but if I sort all the data in column A to put the dates in order, that figure in M1 changes to 298, yet the data has not changed; only the order of the days. I'm not sure how that is possible, as there are still 365 days.

In E1, the subtotal shows 6656792014 when nothing is filtered, yet the number of days (rows) is actually 149999.

So I am at a loss as to what to make of this or how to use it correctly, Anthony. The only cell which works correctly is the array formula in M1, but it only works correctly when the data is NOT filtered. As soon as it is filtered, the figure showing is incorrect. The formula in H1 shows only #VALUE! at all times, and the subtotal in E1 seems to show many more entries than there are.

Possibly it is the fact mine is Excel for Mac, I am not sure.

Thanks so much for your effort

 

[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.

Hi Marcelo

Not sure if this helps at all, but here is a link to one of the files this is to be used in. You can see the figure of 1230 in L5532. That is the number of unique days. When nothing is filtered, that figure seems to be right, but as soon as you filter for anything, the figure changes alright, but it is no longer accurate. Filter down to not many rows showing and you will see what I mean.

So there is lots of pretty irrelevant stuff in this, but the array formula you supplied is in L5532. It is sitting there just to give you a point of reference, as it will actually be located next the pale blue cells in E5531, H5531, E5550 & H5550. The same array formula is in the cells next to each of those, but it is dividing the cell above each into that result. The idea is for it to calculate the number of unique days and divide the total number of selections into that. This then gives me the number of selections per day. So the formula in L5532 was just a guide for me to see how many unique days there actually were, but that is really the only cell which will be of any relevance to you.

Meet Google Drive – One place for all your files

I hope you are able to spot what is stopping the array formula from working correctly. Simply do some filtering using any columns you wish and as the number of rows reduce

cheers

 

[Marcelo Branco]

Hi honkin,

I'm afraid i can't help you anymore (sorry). As i said i do not have access to Excel for Mac - as far i as know Mac-version has some limitations and i'm not sure how it works in a worksheet with so many rows of data and using a complex array formula.

By the way, i couldn't download the file using the link you provided - a password was required.

Good luck.

M.

 

[honkin]

Hi honkin,

I'm afraid i can't help you anymore (sorry). As i said i do not have access to Excel for Mac - as far i as know Mac-version has some limitations and i'm not sure how it works in a worksheet with so many rows of data and using a complex array formula.

By the way, i couldn't download the file using the link you provided - a password was required.

Good luck.

M.

Hi Marcelo

Apologies. Apparently Google had set the account to require a password for all shared files. I just changed it

Odds On Runner 2017 to 2021.xlsx

The file is just an Excel file, by the way. There is nothing specific that makes it for Mac or for Windows. The limitations are in the software, not in the file. This one has only 5000 rows, which was why I shared it, rather than the one with 122k rows. I was just curious if you were able to get it to correctly count the unique days, but totally understand it if you'd prefer not to.

Thanks so much for your help

 

[Marcelo Branco]

honkin

I think I figured out the problem with my formula - it had a flaw (my bad )

See if this works
=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A3:A122337,ROW(A3:A122337)-ROW(A3),0,1)),A3:A122337),A3:A122337),1))
Ctrl+Shift+Enter

M.

 

[Dave Patton]

I do not have your data but you can consider the example that follows
Column D has =COUNTIF($A$3:A3,A3) and I filtered on that column.

Datedif.xlsm
	A	B	C	D
1				5
2	Date	DOW	Num	Column1
3	19-Jan-17	Thursday	0.6076389	1
4	15-01-17			1
10	20-Jan-17	Friday	0.8020833	1
17	24-Jan-17	Tuesday	0.6111111	1
20	25-Jan-17	Wednesday	0.5659722	1
25
1f

Cell Formulas
Range	Formula
D1	D1	=SUMPRODUCT((A3:A25>0)*(SUBTOTAL(102,OFFSET(A3,ROW(A3:A25)-MIN(ROW(A3:A25)),0))))
D20,D17,D10,D3:D4	D3	=COUNTIF($A$3:A3,A3)

 

[honkin]

I do not have your data but you can consider the example that follows
Column D has =COUNTIF($A$3:A3,A3) and I filtered on that column.

Datedif.xlsm
	A	B	C	D
1				5
2	Date	DOW	Num	Column1
3	19-Jan-17	Thursday	0.6076389	1
4	15-01-17			1
10	20-Jan-17	Friday	0.8020833	1
17	24-Jan-17	Tuesday	0.6111111	1
20	25-Jan-17	Wednesday	0.5659722	1
25
1f

Cell Formulas
Range	Formula
D1	D1	=SUMPRODUCT((A3:A25>0)*(SUBTOTAL(102,OFFSET(A3,ROW(A3:A25)-MIN(ROW(A3:A25)),0))))
D20,D17,D10,D3:D4	D3	=COUNTIF($A$3:A3,A3)

hi Dave

Thanks for your reply.

This doesn't appear to work on a large volume of rows. The sheet I have is 5524 rows and when I input your formula, changing A25 to A5524 all the way through, the result is 5522. So it is counting all the rows in the data (the first 2 rows are headers). When I autofilter, it again simply counts the total number of rows less the 2 header rows, rather than identifying the unique entries.

I didn't input the countif formula you supplied in the rest of column D, as it appears not to be referenced anywhere in the sumproduct formula at all. If I have not understood it correctly, please let me know

cheers

 

[Marcelo Branco]

honkin,

Did you see my last post? Post #25
Try it - hope it works

M.

 

[honkin]

honkin

I think I figured out the problem with my formula - it had a flaw (my bad )

See if this works
=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A3:A122337,ROW(A3:A122337)-ROW(A3),0,1)),A3:A122337),A3:A122337),1))
Ctrl+Shift+Enter

M.

ah Marcelo, I think you may be a legend. I have only tested it through 4 or 5 different autofilters, but each time they were correct.

Thanks so much

 

[Marcelo Branco]

ah Marcelo, I think you may be a legend. I have only tested it through 4 or 5 different autofilters, but each time they were correct.

Thanks so much

Wow
Fortunately, I could see what was wrong with my original formula. I hadn't tested that formula properly.
Glad to help

M.