1

I am building a chart that contains the total amount of rows and accounts assigned to a user in Excel 2016. The first issue is easy and I resolved it with a COUNTIF function, however the second part I'm not certain of the best way to do it...

I wish to be able to count the number of unique rows based on two values (I think that's the right way to word this scenario)...

For example, take the following data:

    A                 B
01  Account Number    Assigned User
02  12356             Anthony
03  12356             Anthony
04  12356             Anthony
05  48151             Anthony
06  48151             Anthony
07  20235             Barbara
08  12345             Barbara
09  88514             Anthony
10  89752             Daniel
11  89752             Daniel
12  89752             Daniel

I've been able to count how many unique entries under a column (so 3 total Assigned Users, 6 total Account Numbers), however in this example, I want to calculate how many unique Account Numbers per user.

The expected output I'd want is something like this:

    A                B                 C
01  Assigned User    Total Accounts    Total Rows
02  Anthony          3                 6
03  Barbara          2                 2
04  Daniel           1                 3

Where the "Total Accounts" column would contain the total count of UNIQUE account numbers. For "Total Rows", I'm using the following function: =COUNTIF('Master List'!B:B, A#) (replace A# with A2, A3, A4, etc. for each user name), however how would I structure a function/formula to get the values for Column B, "Total Accounts"?

Improve this question
1

2 Answers 2

Reset to default
1

Select your data (personally I would make this a table but that's not essential)

Insert > PivotTable (first icon on the left of the Insert ribbon)

Choose where to put this eg on a new worksheet or the current one You MUST select the check box for "Add this data to the Data Model". You don't need to worry about the data model, or PowerPivot etc, but this enables "Count Unique" functionality.

Add your Assigned User as rows in the PivotTable, drag Account Number to the values area.

Because Account numbers look like numbers, Excel guesses you want to SUM them. Click the drop-down next to Sum of Account Number and select Value Field Settings. Switch from Sum to Distinct Count (right at the bottom of the list). If you forgot to select the "Add data to Data Model" checkbox, this option is not available.

When your data changes, just refresh the PivotTable to re-evaluate. Watch out if you add more data in case the PT does not include the whole range (this is why I would use a table for the source data, but that's another thread).

Improve this answer
4
  • This worked perfectly! I feel like I've actually done this very same thing once before, figuring it out on my own, but I completely forgot about it. I've been obsessing over functions and formulas; I never considered Pivot Tables. Thank you so much! As for the range, table or no table, I generally pick the entire column and exclude (blank); generally it works fine for me. Is there any reason why I wouldn't want to do this?
    – Anthony LoPrimo
    Commented Jun 1, 2023 at 15:45
  • Selecting whole columns can really drag performance down, Not specifically for PTs but generally I wouldn't advicate doing it. Excluding (blank) is after the fact - it still processed a million rows x N columns. Pick a big number of rows eg 50,000 or use a table, or use a clever named range with an OFFSET and COUNT / COUNTA to return the data and nothing but the data
    – AdamV
    Commented Jun 2, 2023 at 20:06
  • My biggest issue is that I find as me and my team add/remove data from the table, data stops getting processed for PTs and other automated functions at some point. So far, picking the entire column vs specifying some range seems to ensure all rows are processed... are there better ways around this? We use a table, one that lets you drag the corner to resize and add/remove rows or columns, but I can't make it consistently look at all rows of a table (so if one day it's 80 rows, and another day it's 125 rows, sometimes the extra 45 rows might get ignored without re-specifying the rows...)
    – Anthony LoPrimo
    Commented Jun 28, 2023 at 12:44
  • 1
    As long as you refer to the table using table notation eg SomeTable[MyDataColumn] this should just work. I have never known it not to work, to be honest. If you refer to the cells of a table column like cells eg $C2:$C80, then sure, it won't refer to them any other way or extend the range, since that is not what this notation tells Excel to do.
    – AdamV
    Commented Jun 29, 2023 at 22:17
2

As Unique function is not supported on Excel 2016, please try following steps to have a check.

  • For the "Assigned User" column, please copy users' names, then go to Data tab > Data Tools group > Remove Duplicates.
  • For "Total Accounts", please try formula =SUMPRODUCT((1/COUNTIF(A$2:A$12,A$2:A$12))*(B$2:B$12=D2)).

enter image description here

  • For "Total Rows", please try formula =COUNTIF($B$2:$B$12,D2).
Improve this answer
0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .