COUNTIFS Issue w/multiple date conditions
I am attempting to: Count all unique text values in the range: B2:B21 that fall between 2 dates specified in the range A2:A21. Using the following:
=COUNTIFS(DATA!B2:B21,"<>"&" ",DATA!A2:A21,">=1/1/2016", DATA!A2:A21,"<=12/31/2016")
I'm able to get the total count, but haven't sucessfully been able to get a unique count to function.
This has got me hung up slightly.
Any help would be largely appreciated.
Thanks in Advance.
J
Try this...
Data Range
This array formula** entered in B2 and copied down:
=SUM(IF(FREQUENCY(IF(YEAR(DATA!A$2:A$21)=A2,MATCH(DATA!B$2:B$21,DATA!B$2:B$21,0)),ROW(DATA!B$2:B$21)-ROW(DATA!B$2)+1),1))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
This works in this case and most case I will be using this in, but what about if I need to pull a date range within a givien year? Ie. Quarterly Reporting? In this situation the YEAR() function seems far too limiting. What would you recommend?
J
In that case use 2 cells to hold the date range. For example:
A2 = start date
B2 = end date
Then, the formula would be:
=SUM(IF(FREQUENCY(IF(DATA!A$2:A$21>=A2,IF(DATA!A$2:A$21<=B2,MATCH(DATA!B$2:B$21,DATA!B$2:B$21,0))),ROW(DATA!B$2:B$21)-ROW(DATA!B$2)+1),1))
Still array entered.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks