Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

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

1. I agreed to these rules
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

Excel Video Tutorials / Excel Dashboards Reports

2. 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.

3. I agreed to these rules
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. 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.

5. I agreed to these rules
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. 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).

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

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