Count unique values with conditions on large databases

{ 8 Comments }

By Ashish Mathur · · , · Leave a comment

Given a database of 50,000 rows, counting unique values with conditions using formulas would either adversely effect workbook performance or would not work in the first place at all.

In this workbook, I have shown the technique to count unique values with conditions on a large database

1. Using PowerPivot – Will only work in Excel 2010 and higher versions

2. Using a  normal Pivot Table and SUMPRODUCT() function – Will work for all versions but is not as efficient as the PowerPivot solution.

To count unique values with conditions on small databases, you may refer to the following link

Leave a Comment

Your email address will not be published.

CAPTCHA

*

8 Trackbacks

  • Hi Ashis,
    I have a table which contains the following columns –
    City Name, City Code, Customer Name, Customer code, Brand, pack size & Quantity.
    Each brand has different pack size. Like one brand contains 750ML, 375ML, 180ML, 90ML and another one is contain only three pack size 750ML, 375ML, 180ML.
    I want to make a power pivot which show how many unique customers are lifted all Pack size or how many unique customers lifted only one or two, three ….nth pack size.

    I haven’t give you the complete file due it is very heavy 800MB size csv file which is very large customer database. I have give a sample of that data and require result.

    file link is as following-
    https://onedrive.live.com/redir?resid=354CE60AA62E9D4C!147&authkey=!ABcCoG__6IjTAKI&ithint=file%2c.xlsx

    Reply