• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

count of unique values that meet specific criteria

I

indesignkat

Member
Hello,

I feel like I'm thinking too hard about this and skipping right past the easy answer, but here goes:

I have a spreadsheet of roughly 5,000 rows and around 25 columns. One of the columns contains a customer ID, another contains a transaction ID, another contains a geographic region, and another contains a word indicating the status of the transaction. One customer can have numerous transactions, of varying statuses. I am trying to get a count of unique customers with a given status in a given region.

For example:
USA 1111 500000 Active
USA 1133 500001 Closed
USA 1111 500002 Active
MEX 2222 500003 Closed
MEX 2222 500004 Active

In the data above, USA has 2 unique customers, but only 1 active unique customer. USA also has 3 unique transactions, but only 2 active unique transactions.

This data is presented in a dashboard that updates automatically whenever you click on a geographic region or a status word, refining the visuals for that region and that status. I've got all that ready, but for some reason counting unique records that meet specific criteria is escaping me.
 
Narayan,

Thank you for the response. That works, but it does take quite a while to process on a medium-large range of data like I have. I also realized that for my project I only need to count the unique active customers, not unique active transactions.

I think I've sorted out a faster way to get the desired effect by using a helper column in the source data. It checks to see if the current row matches the criteria and if so, divides 1 by the count of instances of rows that match the criteria for this account. So a row with an account that has 4 rows meeting criteria will return a 0.25.

Here's the formula:

=IFERROR(1/IF($D2=$K$2,SUMPRODUCT(($B$2:$B$6=$B2)*($D$2:$D$6=$K$2)),0),0)

Where, as in the sample file, column D has the Status, B has the Customer #, and K2 has the currently selected status criteria.

Then just sumif on that column for rows with region (column A) matching the selected criteria (column G)

Thank you for the formula, though, I do find that very useful.

idk
 
You must log in or register to reply here.
Back
Top