Hi All, I found a formula to count unique values between dates here - https://www.extendoffice.com/documen...een-dates.html - but can't seem to find a formula that will do this on a filtered sheet.
Can anyone point me in the right direction?
Many thanks in advance.
Dave C
Try this:
=SUM(IF(FREQUENCY(IF(SUBTOTAL(103,OFFSET(A2,ROW(A2:A8)-ROW(A2),,1)),IF((B2:B8>=E1)*(B2:B8<=E2),MATCH("~"&A2:A8,A2:A8&"",0))),ROW(A2:A8)-ROW(A2)+1),1)) Ctrl Shift Enter
Source:
https://www.extendoffice.com/documen...ed-column.html
Alternatively, you can create a helper column (I put it into column Z) using this formula:
Z2 =--(AND(B2>=E$1,B2<=E$2,COUNTIF(A$2:A2,A2)=1))
After dragging that formula down through Z8, you can use this simple SUBTOTAL formula to return the desired result:
=SUBTOTAL(109,Z2:Z8)
Note that this is assuming the same structure as the data in your link (ID's in column A, Dates in column B, and start/end date in E2 and E3.
Hi 63falcondude. Thanks for the reply and useful formula. Ironic that you found it at the same site!
I have used the first formula with success as I would rather avoid using helper columns but am having difficulty translating the formula into VBA with a variable row. I will post this new issue in the VBA forum and link them once posted.
The link to my VBA query for this formula is here: https://www.excelforum.com/excel-pro...ml#post4888057
Many thanks
Last edited by L plates; 04-25-2018 at 05:49 AM. Reason: Added link to related VBA query
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks