+ Reply to Thread
Results 1 to 7 of 7

Unique Customers per month from table in graph form

  1. 08-31-2015, 10:33 AM #1
    faodavid
    faodavid is offline
    Forum Contributor
    Join Date
    09-02-2014
    Location
    England
    MS-Off Ver
    2013
    Posts
    116

    Unique Customers per month from table in graph form

    Hi
    I have a table where I records band bookings made in different venues across the country - I want to visually record (in graph format) how many new and unique venues I am getting per month, the total value spent to date by each venue and the number of bookings made to date by each venue in graph format - could anyone explain how this could be done. The actual table has records going back a year with a few hundred entries. Ive attached just a sample

    Thanks in advance
    David
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 08-31-2015, 10:39 AM #2
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Unique Customers per month from table in graph form

    Do you want this to be new venues booked regardless of band? Is there a difference between new and unique and if so what is it?
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W
    Reply With QuoteRegister To Reply
  3. 08-31-2015, 11:23 AM #3
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Unique Customers per month from table in graph form

    Taking a guess that you just want new venues, this assigns a value of 1 to a venue if it is the first time used and a 0 is more than one. I added a column called Visits and entered this formula to count the visits to the venue:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Then I used a Pivot table and and grouped the dates by month and summed the visits (new visits). A column chart was then formed from the Pivot table data.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 09-01-2015, 01:39 PM #4
    faodavid
    faodavid is offline
    Forum Contributor
    Join Date
    09-02-2014
    Location
    England
    MS-Off Ver
    2013
    Posts
    116

    Re: Unique Customers per month from table in graph form

    Hi
    Thanks so much for this. Is it possible to graph the number of shows as a total at each venue and the total fees earned for each venue year to date?
    Reply With QuoteRegister To Reply
  5. 09-01-2015, 03:11 PM #5
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Unique Customers per month from table in graph form

    I created another Pivot Table for you counting the visits to a venue and the total fee. Charts, to me, looked very bad and not useable.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 09-05-2015, 02:38 PM #6
    faodavid
    faodavid is offline
    Forum Contributor
    Join Date
    09-02-2014
    Location
    England
    MS-Off Ver
    2013
    Posts
    116

    Re: Unique Customers per month from table in graph form

    Hi, thanks once again
    I think I need a slight tweak to the following formula you provided =IF(COUNTIF($D$2:D2,D2)=1,1,0) as I think this just records the venue name - the problem being there could be two or more venues using the same name but located in different cities all of which would be unique the first time a show happens there. Is there a way to combine city and venue name in the formula to solve this?
    Thanks as ever
    Reply With QuoteRegister To Reply
  7. 09-05-2015, 02:52 PM #7
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Unique Customers per month from table in graph form

    Change the formula to the following that includes the city and venue name in the calculations:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Determine repeat customers vs. new customers based on purchase date
    By mktgdude in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-02-2013, 04:10 AM
  2. Counting # Of Unique Items In A 12 Month Period (Table)
    By unpluggedmusic in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-13-2013, 09:39 AM
  3. Excel 2007 : Finding Unique Customers in a column for each sale rep through formula
    By Raheel Fazal in forum Excel General
    Replies: 2
    Last Post: 06-04-2012, 01:02 AM
  4. counting unique customers in a list by a specific date range
    By perducci in forum Excel General
    Replies: 3
    Last Post: 03-08-2012, 01:07 PM
  5. Counting unique customers filtered by date
    By perducci in forum Excel General
    Replies: 2
    Last Post: 03-08-2012, 12:49 PM
  6. Finding unique number of customers
    By fibaroobi in forum Excel General
    Replies: 0
    Last Post: 08-25-2010, 11:39 PM
  7. Number of unique customers for every possible product combination? (2007 CSE)
    By JayUSA in forum Excel General
    Replies: 5
    Last Post: 01-16-2010, 01:17 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1