Counting unique values in one column based on criteria in another column with result in a single cell

[Monomeeth]

Ok, have been struggling with this for a few hours, so am hoping someone can help me. I've designed an Excel Dashboard that displays a number of charts and tables based on a user selecting a month from a dropdown. Everything works fine, but today I wanted to add another category onto the Dashboard to provide some miscellaneous data.

For example, the first item at N36 is for "Total visitors up to selected month" and the second item at N37 is for "Unique visitors up to selected month", and so on.

I've got a formula in cell N36 that does the job for item 1 (although it's a very long formula with almost 2800 characters). Might be worth a separate post to see if there's a more elegant way to achieve the same result - but I digress!

Anyway, in cell N37 I want to add a formula that effectively counts UNIQUE values on another worksheet (let's just call it "DATA"). So, in the DATA sheet I want to count the number of unique values in column C based on criteria applied to column F.

All data in column F is entered as either "August 2014", "September 2014" and so on until "May 2015". The DATA sheet has over 20,000 rows of data. Below is a dummy example of sheet DATA:

[TABLE="class: grid, width: 500, align: left"]
<TBODY>[TR]
[TD]First Name
[/TD]
[TD]Surname
[/TD]
[TD]User ID
[/TD]
[TD]Line
[/TD]
[TD]Group
[/TD]
[TD]Month
[/TD]
[/TR]
[TR]
[TD]Fred
[/TD]
[TD]Flintstone
[/TD]
[TD]abcde
[/TD]
[TD]CAR
[/TD]
[TD]TRAN
[/TD]
[TD]September 2014
[/TD]
[/TR]
[TR]
[TD]Homer
[/TD]
[TD]Simpson
[/TD]
[TD]acfge
[/TD]
[TD]BOO
[/TD]
[TD]LITE
[/TD]
[TD]January 2015
[/TD]
[/TR]
[TR]
[TD]James
[/TD]
[TD]Bond
[/TD]
[TD]ab007
[/TD]
[TD]BOA
[/TD]
[TD]TRAN
[/TD]
[TD]May 2015
[/TD]
[/TR]
[TR]
[TD]George
[/TD]
[TD]Jetson
[/TD]
[TD]acqwe
[/TD]
[TD]CAR
[/TD]
[TD]TRAN
[/TD]
[TD]August 2014
[/TD]
[/TR]
[TR]
[TD]Fred
[/TD]
[TD]Flintstone
[/TD]
[TD]abcde
[/TD]
[TD]CAR
[/TD]
[TD]TRAN
[/TD]
[TD]October 2014
[/TD]
[/TR]
[TR]
[TD]James
[/TD]
[TD]Bond
[/TD]
[TD]ab007
[/TD]
[TD]BOA
[/TD]
[TD]TRAN
[/TD]
[TD]February 2015
[/TD]
[/TR]
[TR]
[TD]Homer
[/TD]
[TD]Simpson
[/TD]
[TD]acfge
[/TD]
[TD]BOO
[/TD]
[TD]LITE
[/TD]
[TD]November 2014
[/TD]
[/TR]
[TR]
[TD]Homer
[/TD]
[TD]Simpson
[/TD]
[TD]acfge
[/TD]
[TD]BOO
[/TD]
[TD]LITE
[/TD]
[TD]December 2014
[/TD]
[/TR]
[TR]
[TD]George
[/TD]
[TD]Jetson
[/TD]
[TD]acqwe
[/TD]
[TD]CAR
[/TD]
[TD]TRAN
[/TD]
[TD]March 2015
[/TD]
[/TR]
[TR]
[TD]George
[/TD]
[TD]Jetson
[/TD]
[TD]acqwe
[/TD]
[TD]CAR
[/TD]
[TD]TRAN
[/TD]
[TD]April 2015
[/TD]
[/TR]
[TR]
[TD]Fred
[/TD]
[TD]Flintstone
[/TD]
[TD]abcde
[/TD]
[TD]CAR
[/TD]
[TD]TRAN
[/TD]
[TD]January 2015
[/TD]
[/TR]
</TBODY>[/TABLE]

So, what I need in cell N37 of my Dashboard Sheet is a formula that will count the unique number of values in column C (i.e. the User ID column) based on criteria in column F that is determined by the user's selection in a dropdown (which places a value in cell L1)

For example, if the user has selected "August 2014" from the dropdown, then cell L1 displays "August 2014". So the formula in cell N37 is such that it will count all the unique values in column C of the "DATA" sheet that contain "August 2014" in the row at column F. However, the trick is that the criteria is cumulative. By that I mean that if the user selects "September 2014" then the formula needs to count all the unique values in column C of the "DATA" sheet that contain either "August 2014" or "September 2014" in the row at column F. Likewise, if they select "October 2014" then the formula needs to count all the unique values in column C of the "DATA" sheet that contain either "August 2014" or "September 2014" or "October 2014" in the row at column F.

This is because this cell is measuring "Unique visitors up to selected month". In this way it is designed to measure the number of unique visitors over the entire period. So, if a user visits the site every day from August 2014 until May 2015, they only get counted once. However, if the same user only visited the site once and that was in November 2014, then they aren't counted at all unless the Dashboard user has selected November 2014 or a later month.

I hope this all makes sense. LOL

Anyway, let's see how we go.

Thanks everyone!

 

[oldbrewer]

[TABLE="width: 960"]
<colgroup><col width="64" span="15" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]First Name[/TD]
[TD="width: 64"]Surname[/TD]
[TD="width: 64"]User ID[/TD]
[TD="width: 64"]Line[/TD]
[TD="width: 64"]Group[/TD]
[TD="width: 64"]Month[/TD]
[TD="width: 64"]code1[/TD]
[TD="width: 64"]included[/TD]
[TD="class: xl23, width: 64"]unique in included dates ?[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]Flintstone[/TD]
[TD]abcde[/TD]
[TD]CAR[/TD]
[TD]TRAN[/TD]
[TD="class: xl22, align: right"]Sep-14[/TD]
[TD="align: right"]9[/TD]
[TD]yes[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Homer[/TD]
[TD]Simpson[/TD]
[TD]acfge[/TD]
[TD]BOO[/TD]
[TD]LITE[/TD]
[TD="class: xl22, align: right"]Jan-15[/TD]
[TD="align: right"]13[/TD]
[TD]yes[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]Bond[/TD]
[TD]ab007[/TD]
[TD]BOA[/TD]
[TD]TRAN[/TD]
[TD="class: xl22, align: right"]May-15[/TD]
[TD="align: right"]17[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]Jetson[/TD]
[TD]acqwe[/TD]
[TD]CAR[/TD]
[TD]TRAN[/TD]
[TD="class: xl22, align: right"]Aug-14[/TD]
[TD="align: right"]8[/TD]
[TD]yes[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]Flintstone[/TD]
[TD]abcde[/TD]
[TD]CAR[/TD]
[TD]TRAN[/TD]
[TD="class: xl22, align: right"]Oct-14[/TD]
[TD="align: right"]10[/TD]
[TD]yes[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]Bond[/TD]
[TD]ab007[/TD]
[TD]BOA[/TD]
[TD]TRAN[/TD]
[TD="class: xl22, align: right"]Feb-15[/TD]
[TD="align: right"]14[/TD]
[TD]yes[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Homer[/TD]
[TD]Simpson[/TD]
[TD]acfge[/TD]
[TD]BOO[/TD]
[TD]LITE[/TD]
[TD="class: xl22, align: right"]Nov-14[/TD]
[TD="align: right"]11[/TD]
[TD]yes[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Homer[/TD]
[TD]Simpson[/TD]
[TD]acfge[/TD]
[TD]BOO[/TD]
[TD]LITE[/TD]
[TD="class: xl22, align: right"]Dec-14[/TD]
[TD="align: right"]12[/TD]
[TD]yes[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]Jetson[/TD]
[TD]acqwe[/TD]
[TD]CAR[/TD]
[TD]TRAN[/TD]
[TD="class: xl22, align: right"]Mar-15[/TD]
[TD="align: right"]15[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]Jetson[/TD]
[TD]acqwe[/TD]
[TD]CAR[/TD]
[TD]TRAN[/TD]
[TD="class: xl22, align: right"]Apr-15[/TD]
[TD="align: right"]16[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]Flintstone[/TD]
[TD]abcde[/TD]
[TD]CAR[/TD]
[TD]TRAN[/TD]
[TD="class: xl22, align: right"]Jan-15[/TD]
[TD="align: right"]13[/TD]
[TD]yes[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]Flintstone[/TD]
[TD]abcde[/TD]
[TD]CAR[/TD]
[TD]TRAN[/TD]
[TD="class: xl22, align: right"]Sep-14[/TD]
[TD="align: right"]9[/TD]
[TD]yes[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Homer[/TD]
[TD]Simpson[/TD]
[TD]acfge[/TD]
[TD]BOO[/TD]
[TD]LITE[/TD]
[TD="class: xl22, align: right"]Jan-15[/TD]
[TD="align: right"]13[/TD]
[TD]yes[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]Bond[/TD]
[TD]ab007[/TD]
[TD]BOA[/TD]
[TD]TRAN[/TD]
[TD="class: xl22, align: right"]May-15[/TD]
[TD="align: right"]17[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]code1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]Jetson[/TD]
[TD]acqwe[/TD]
[TD]CAR[/TD]
[TD]TRAN[/TD]
[TD="class: xl22, align: right"]Aug-14[/TD]
[TD="align: right"]8[/TD]
[TD]yes[/TD]
[TD]no[/TD]
[TD][/TD]
[TD="class: xl22, align: right"]Aug-14[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]Unique[/TD]
[TD]zzzzz[/TD]
[TD]CAR[/TD]
[TD]TRAN[/TD]
[TD="class: xl22, align: right"]Oct-14[/TD]
[TD="align: right"]10[/TD]
[TD]yes[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD="class: xl22, align: right"]Sep-14[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]Bond[/TD]
[TD]ab007[/TD]
[TD]BOA[/TD]
[TD]TRAN[/TD]
[TD="class: xl22, align: right"]Feb-15[/TD]
[TD="align: right"]14[/TD]
[TD]yes[/TD]
[TD]no[/TD]
[TD][/TD]
[TD="class: xl22, align: right"]Oct-14[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Homer[/TD]
[TD]Simpson[/TD]
[TD]acfge[/TD]
[TD]BOO[/TD]
[TD]LITE[/TD]
[TD="class: xl22, align: right"]Nov-14[/TD]
[TD="align: right"]11[/TD]
[TD]yes[/TD]
[TD]no[/TD]
[TD][/TD]
[TD="class: xl22, align: right"]Nov-14[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Homer[/TD]
[TD]Simpson[/TD]
[TD]acfge[/TD]
[TD]BOO[/TD]
[TD]LITE[/TD]
[TD="class: xl22, align: right"]Dec-14[/TD]
[TD="align: right"]12[/TD]
[TD]yes[/TD]
[TD]no[/TD]
[TD][/TD]
[TD="class: xl22, align: right"]Dec-14[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]Jetson[/TD]
[TD]acqwe[/TD]
[TD]CAR[/TD]
[TD]TRAN[/TD]
[TD="class: xl22, align: right"]Mar-15[/TD]
[TD="align: right"]15[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl22, align: right"]Jan-15[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]Jetson[/TD]
[TD]acqwe[/TD]
[TD]CAR[/TD]
[TD]TRAN[/TD]
[TD="class: xl22, align: right"]Apr-15[/TD]
[TD="align: right"]16[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl22, align: right"]Feb-15[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]Flintstone[/TD]
[TD]abcde[/TD]
[TD]CAR[/TD]
[TD]TRAN[/TD]
[TD="class: xl22, align: right"]Jan-15[/TD]
[TD="align: right"]13[/TD]
[TD]yes[/TD]
[TD]no[/TD]
[TD][/TD]
[TD="class: xl22, align: right"]Mar-15[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl22, align: right"]Apr-15[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]17[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl22, align: right"]May-15[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]select a month[/TD]
[TD="class: xl22, align: right"]Feb-15[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 6"]column I looks for unique id's only in the defined date range[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]formula in I2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 6"]=IF(H2="yes",IF(COUNTIF($C$2:$C$23,C2)=1,"yes","no"),"")[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Monomeeth]

Hi oldbrewer, thanks for your response. I really appreciate it!

I think I must be missing something, or haven't explained my initial query very well.

Looking at your response I see you've added columns and you've given me the formula you're using in Column I, but I don't see how that helps me (hence why I'm not sure I've explained myself very well). So I'll clarify below.

In a nutshell, the user only sees one sheet "Dashboard" and I need a formula for cell N37 of that sheet. The formula will count all the unique visitors for a period of time. The period of time is based on the user selecting a month from a drop down menu (also on the Dashboard sheet) and this selection populates cell L1 (also on the Dashboard sheet). That is, if the user selects "December 2014" in the dropdown, then "December 2014" appears in cell L1.

So, what I need is a formula that counts the unique visitors listed at column C of another sheet (let's call this sheet "Data"). But the tricky bit is that I need it to count these unique visitors based on the period of time selected by the user. To help facilitate this, I've set up column F in the Data sheet to display the month. Below are two examples to further clarify.

Example 1: Simple

The user selects "August 2014". This is the first (and oldest) month on the list. The formula I need would then count all the unique visitors (listed at column C of the Data sheet) that have "August 2014" listed at column F of the Data sheet. In my actual workbook, this would return a value of 634 (i.e. I have 634 unique visitors for August 2014).


Example 2: Complicated

The user selects "February 2015". This is the seventh month on the list. The formula I need would then count all the unique visitors (listed at column C of the Data sheet) that have either "August 2014", "September 2014", "October 2014", "November 2014", "December 2014", "January 2014" or "February 2014" listed at column F of the Data sheet. In my actual workbook there are 14,209 rows in the Data sheet that contain one of the seven months listed. However, for that entire period (i.e. during the seven month period from Aug '14 til Feb '15) there were 6,389 unique visitors. That's because anyone who visited the site during that entire period is only counted once, regardless of whether they visited the site once or 10,000 times, regardless of whether they visited the site only in August 2014 or visited the site in each of the seven months. So, the formula I need would need to calculate a result of 6,389 if the user selected "February 2015", because that's the number of unique users for the entire period.


Additional comments
The Data sheet This sheet currently has over 20,000 rows of data and already lists only unique visitors by month. That is, using May 2015 as an example, during this month we had 37,838 views from 5,825 visitors. However, the number of unique visitors for the month was only 2,282. Therefore, the "Data" sheet only contains 2,282 rows of data for May 2015. I'm guessing I didn't make this clear earlier.

Another formula As I mentioned in my opening post, cell N36 displays Total visitors up to selected month. If it helps, the formula I have used in cell N36 of the Dashboard sheet is as follows:

=IF(L1="August 2014",COUNTIF(UniqueVisitors[Month],"August 2014"),IF(L1="September 2014",COUNTIF(UniqueVisitors[Month],"August 2014")+COUNTIF(UniqueVisitors[Month],"September 2014"),IF(L1="October 2014",COUNTIF(UniqueVisitors[Month],"August 2014")+COUNTIF(UniqueVisitors[Month],"September 2014")+COUNTIF(UniqueVisitors[Month],"October 2014"),IF(L1="November 2014",COUNTIF(UniqueVisitors[Month],"August 2014")+COUNTIF(UniqueVisitors[Month],"September 2014")+COUNTIF(UniqueVisitors[Month],"October 2014")+COUNTIF(UniqueVisitors[Month],"November 2014"),IF(L1="December 2014",COUNTIF(UniqueVisitors[Month],"August 2014")+COUNTIF(UniqueVisitors[Month],"September 2014")+COUNTIF(UniqueVisitors[Month],"October 2014")+COUNTIF(UniqueVisitors[Month],"November 2014")+COUNTIF(UniqueVisitors[Month],"December 2014"),IF(L1="January 2015",COUNTIF(UniqueVisitors[Month],"August 2014")+COUNTIF(UniqueVisitors[Month],"September 2014")+COUNTIF(UniqueVisitors[Month],"October 2014")+COUNTIF(UniqueVisitors[Month],"November 2014")+COUNTIF(UniqueVisitors[Month],"December 2014")+COUNTIF(UniqueVisitors[Month],"January 2015"),IF(L1="February 2015",COUNTIF(UniqueVisitors[Month],"August 2014")+COUNTIF(UniqueVisitors[Month],"September 2014")+COUNTIF(UniqueVisitors[Month],"October 2014")+COUNTIF(UniqueVisitors[Month],"November 2014")+COUNTIF(UniqueVisitors[Month],"December 2014")+COUNTIF(UniqueVisitors[Month],"January 2015")+COUNTIF(UniqueVisitors[Month],"February 2015"),IF(L1="March 2015",COUNTIF(UniqueVisitors[Month],"August 2014")+COUNTIF(UniqueVisitors[Month],"September 2014")+COUNTIF(UniqueVisitors[Month],"October 2014")+COUNTIF(UniqueVisitors[Month],"November 2014")+COUNTIF(UniqueVisitors[Month],"December 2014")+COUNTIF(UniqueVisitors[Month],"January 2015")+COUNTIF(UniqueVisitors[Month],"February 2015")+COUNTIF(UniqueVisitors[Month],"March 2015"),IF(L1="April 2015",COUNTIF(UniqueVisitors[Month],"August 2014")+COUNTIF(UniqueVisitors[Month],"September 2014")+COUNTIF(UniqueVisitors[Month],"October 2014")+COUNTIF(UniqueVisitors[Month],"November 2014")+COUNTIF(UniqueVisitors[Month],"December 2014")+COUNTIF(UniqueVisitors[Month],"January 2015")+COUNTIF(UniqueVisitors[Month],"February 2015")+COUNTIF(UniqueVisitors[Month],"March 2015")+COUNTIF(UniqueVisitors[Month],"April 2015"),IF(L1="May 2015",COUNTIF(UniqueVisitors[Month],"August 2014")+COUNTIF(UniqueVisitors[Month],"September 2014")+COUNTIF(UniqueVisitors[Month],"October 2014")+COUNTIF(UniqueVisitors[Month],"November 2014")+COUNTIF(UniqueVisitors[Month],"December 2014")+COUNTIF(UniqueVisitors[Month],"January 2015")+COUNTIF(UniqueVisitors[Month],"February 2015")+COUNTIF(UniqueVisitors[Month],"March 2015")+COUNTIF(UniqueVisitors[Month],"April 2015")+COUNTIF(UniqueVisitors[Month],"May 2015"),"?"))))))))))


Obviously the above formula is referencing named ranges, but you get the idea.

Hopefully this all helps to clarify things rather than confuse matters more!

 

[oldbrewer]

my "solution" allowed the user to select a month and then for that month and all previous months it searched for people that had only visited once, that was the misunderstanding. I will give it some more thought..........

 

[oldbrewer]

[TABLE="width: 854"]
<colgroup><col><col><col><col span="4"><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD]id[/TD]
[TD]date visited[/TD]
[TD]is date in range[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1299[/TD]
[TD="align: right"]01/01/2015[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1235[/TD]
[TD="align: right"]05/01/2015[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1236[/TD]
[TD="align: right"]09/01/2015[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/02/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1237[/TD]
[TD="align: right"]13/01/2015[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/03/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1238[/TD]
[TD="align: right"]17/01/2015[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/04/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1239[/TD]
[TD="align: right"]21/01/2015[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/05/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1240[/TD]
[TD="align: right"]25/01/2015[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/06/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1241[/TD]
[TD="align: right"]29/01/2015[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/07/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1242[/TD]
[TD="align: right"]02/02/2015[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1243[/TD]
[TD="align: right"]06/02/2015[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1244[/TD]
[TD="align: right"]10/02/2015[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1234[/TD]
[TD="align: right"]14/02/2015[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1235[/TD]
[TD="align: right"]18/02/2015[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1236[/TD]
[TD="align: right"]22/02/2015[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]unique visitors before this date[/TD]
[TD="align: right"]01/05/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1237[/TD]
[TD="align: right"]26/02/2015[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1238[/TD]
[TD="align: right"]02/03/2015[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]could easily be selected by a drop down[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1239[/TD]
[TD="align: right"]06/03/2015[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1240[/TD]
[TD="align: right"]10/03/2015[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1241[/TD]
[TD="align: right"]14/03/2015[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1242[/TD]
[TD="align: right"]18/03/2015[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1243[/TD]
[TD="align: right"]22/03/2015[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]is date in range[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1244[/TD]
[TD="align: right"]26/03/2015[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1245[/TD]
[TD="align: right"]30/03/2015[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Count of id[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1246[/TD]
[TD="align: right"]03/04/2015[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]id[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1239[/TD]
[TD="align: right"]07/04/2015[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1234[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1240[/TD]
[TD="align: right"]11/04/2015[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1235[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1241[/TD]
[TD="align: right"]15/04/2015[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1236[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1242[/TD]
[TD="align: right"]19/04/2015[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1237[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1243[/TD]
[TD="align: right"]23/04/2015[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1238[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1244[/TD]
[TD="align: right"]27/04/2015[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1239[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1234[/TD]
[TD="align: right"]01/05/2015[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1240[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1235[/TD]
[TD="align: right"]05/05/2015[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1241[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1236[/TD]
[TD="align: right"]09/05/2015[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1242[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1237[/TD]
[TD="align: right"]13/05/2015[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1243[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1238[/TD]
[TD="align: right"]17/05/2015[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1244[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1239[/TD]
[TD="align: right"]21/05/2015[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1245[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1240[/TD]
[TD="align: right"]25/05/2015[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1246[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1234[/TD]
[TD="align: right"]29/05/2015[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1299[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1235[/TD]
[TD="align: right"]02/06/2015[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Grand Total[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1236[/TD]
[TD="align: right"]06/06/2015[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]the pivot table lists each id for the selected timescale[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]as you say the total number of visits is irrelevant[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]