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:
<TBODY>
</TBODY>
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!
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:
| First Name | Surname | User ID | Line | Group | Month |
| Fred | Flintstone | abcde | CAR | TRAN | September 2014 |
| Homer | Simpson | acfge | BOO | LITE | January 2015 |
| James | Bond | ab007 | BOA | TRAN | May 2015 |
| George | Jetson | acqwe | CAR | TRAN | August 2014 |
| Fred | Flintstone | abcde | CAR | TRAN | October 2014 |
| James | Bond | ab007 | BOA | TRAN | February 2015 |
| Homer | Simpson | acfge | BOO | LITE | November 2014 |
| Homer | Simpson | acfge | BOO | LITE | December 2014 |
| George | Jetson | acqwe | CAR | TRAN | March 2015 |
| George | Jetson | acqwe | CAR | TRAN | April 2015 |
| Fred | Flintstone | abcde | CAR | TRAN | January 2015 |
<TBODY>
</TBODY>
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!
