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
Account Number Assigned User
01 12356 Anthony
02 12356 Anthony
03 12356 Anthony
04 48151 Anthony
05 48151 Anthony
06 20235 Barbara
07 12345 Barbara
08 88514 Anthony
09 89752 Daniel
10 89752 Daniel
11 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
1 Assigned User Total Accounts Total Rows
2 Anthony 3 6
3 Barbara 2 2
4 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”?
