Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Counting unique values in one column, based on criteria determined by another column

  1. #1
    Join Date
    5th February 2013
    Posts
    4

    Counting unique values in one column, based on criteria determined by another column

    Apologies for replicating any similar posts, but I have read through many and not understood how to apply the solutions given.To make things easier I have uploaded a sample file which is a substantially reduced version of my excel with some sample data.

    The basic goal of this is to establish how many unique customers we have placing orders at any given time through each financial year so that we can say for example; by January in the financial year of 2012 we had 100 orders from 50 unique customers, however in January of financial year 2012 we only had 150 orders from 70 unique customers.

    The single worksheet has 4 columns of data plus a small table that I wish to populate. I want to populate the table based on the following:

    - For each month of the given financial year, with each year being a new row, I wish to count how many customers in that month placed their first order for that financial year. Therefore, at the end of the table I should be able to sum the months of that year to give me the total number of of customers who placed their first order that year.

    - Each year should be treated individually, disregarding whether a customer placed an order in the prior year.

    Does this make sense? Please ask me any questions and I will clarify if necessary.

    Thanks,
    James
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    23rd June 2005
    Location
    Ontario, Canada
    Posts
    3,985

    Re: Counting unique values in one column, based on criteria determined by another col

    I would add a helper column to determine all the first order customers for the year

    so in E2 enter formula:

    =IF(COUNTIFS(C$2:C2,C2,D$2:D2,D2)=1,TEXT(B2,"mmmm")&D2,"")

    copied down. This will concatenate the month and financial year for easy counting later.

    Then in G5:

    =COUNTIF($E:$E,TEXT(G$4,"mmmm")&$F5)

    copied across the months and down the years.

    Hope that works for you.
    Microsoft MVP - Excel

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  3. #3
    Join Date
    5th February 2013
    Posts
    4

    Re: Counting unique values in one column, based on criteria determined by another col

    Thanks, that seemed to work! I have to admit, I can't really follow what is going on, could you explain what it is actually doing? I don't really get the COUNTIF and how it works in this formula. Your help is much appreciated.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    23rd June 2005
    Location
    Ontario, Canada
    Posts
    3,985

    Re: Counting unique values in one column, based on criteria determined by another col

    Ok, the first one is the helper column formula:

    =IF(COUNTIFS(C$2:C2,C2,D$2:D2,D2)=1,TEXT(B2,"mmmm")&D2,"")

    this uses COUNTIFS() which checks multiple conditions at once and counts the matches.

    So we check the count of matches to both column C and D values from the top to the current row of the formula. As we copy formula down, the $ in the formula make sure to keep the top row reference frozen so we always count from there. We are looking for the first time that a customer id/fiscal year combination appears (i.e. when the COUNTIFS() yields 1). When we find that first one, we want a result that we can easily match up to the criteria you have to check (i.e. month vs year). So the TEXT() function extracts the Month name from the date in column B and we concatenate the fiscal year to that month. The blanks mean that the customer has already appeared with this fiscal year, so it shouldn't be counted.

    The second formula that gives your final results uses COUNTIF() i.e. without the S at the end. This checks a single condition only and counts matches.

    So since you have dates in the headers that are formatted to show month name, we need to convert that to a text string for the month name, so we employ again the TEXT() function to do that, then we concatenate the year in that row.. .and we match that up to the helper column to count matches.

    You can learn lots more on the internet for COUNTIF() and COUNTIFS() functions. Here is one such place: http://www.contextures.com/xlFunctions04.html

    Hope that helps.
    Microsoft MVP - Excel

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  5. #5
    Join Date
    5th February 2013
    Posts
    4

    Re: Counting unique values in one column, based on criteria determined by another col

    Thanks again, that makes complete sense. The bit I was struggling with was how kept duplicate fields blank, but on ready your explanation it's clear. First time on this forum but if the help is a useful as this I'm sure I'll be back - haha.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    Re: Counting unique values in one column, based on criteria determined by another col

    Another *VERY* useful, but often overlooked and definitely underutilized, tool to help understand how formulas work is the Evaluate Formula tool built in to Excel

    In conjunction with that tool, you really need to read up on the syntax and arguments in each function so when the formula is evaluated youu can correlate the results with the arguments in the various nested functions (or any function nested or not).
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 1
    Last Post: September 6th, 2012, 23:55
  2. Replies: 16
    Last Post: August 18th, 2012, 03:18
  3. Replies: 1
    Last Post: March 28th, 2012, 15:28
  4. Replies: 2
    Last Post: October 6th, 2010, 21:52
  5. Counting Number Of Unique Values In A Column
    By shevtsov in forum EXCEL HELP
    Replies: 6
    Last Post: December 1st, 2006, 04:26

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
  •  
porno