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

 

[Dave Patton]

re my post #26 formula =COUNTIF($A$3:A3,A3)

Enter the formula in the first cell and copy it down
It shows 1 for the first instance of each date

 

[Anthony47]

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

The workbook I shared returns valid results using the formula =CountDays(A2;E1) (the one set in H1) or =CountDays(A2) These formulas don't need to be confirmed by CSE.
I use Office 365, but the file don't use any feature unavailable to standard editions, and indeed I tested it also on Office 2010

If my original file doesn't work for you then there should be some incompatibility with the Mac environment; for example I don't know how UDFs performs with MacOs.
Since you already got from Marcelo the working solution, it would be useless to further investigate the problem.

The formula in M1 is the old one proposed by Marcelo; it was there because I wanted to test it with my data; I deleted it and resaved the file, so it would not confuse further.

Bye

 

[Anthony47]

If my original file doesn't work for you then there should be some incompatibility with the Mac environment; for example I don't know how UDFs performs with MacOs.
Since you already got from Marcelo the working solution, it would be useless to further investigate the problem.

My UDF uses the "Scripting.Dictionary", a data structure made available by the Microsoft Scripting Runtime library, scrrun.dll; the "open" version is "VBA-Dictionary", than can be downloaded from GitHub:

GitHub - VBA-tools/VBA-Dictionary: Drop-in replacement for Scripting.Dictionary on Mac

Drop-in replacement for Scripting.Dictionary on Mac - VBA-tools/VBA-Dictionary

github.com

I shall modify the published file to clarify this limitation.

Bye

 

[honkin]

re my post #26 formula =COUNTIF($A$3:A3,A3)

Enter the formula in the first cell and copy it down
It shows 1 for the first instance of each date

Cheers Dave. I knew what the formula was doing, just couldn't see any use for it, as the full formula never seemed to reference that column; only the column which had the date in it, so a helper column like that served no purpose for this task as there are ways to do what I am after without adding another column.

Thanks again for your input, though