COUNTIFS that only counts unique values

[tkoral]

Hi there,

I'm currently trying to write a formula to count the total number of alpha-numeric codes, per person, per month. The problem I have is, some of the codes repeat themselves, so I can't seem to figure out how to count the unique occurrences, per person.

I've attached a rough sample.

Thank you SO much for any and all help.

[AliGW]

Try this:

=SUMPRODUCT(($A$2:$A$14=E2)*($B$2:$C$14=E1)*($B$1:$C$1=F1))

These are not really unique values, however ...

[Richard Buttrey]

Hi,

This is clearly a much simplified example and probably doesn't represent the size of your actual data. We often find that when we give an answer to what is a trivial non representative example, when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned. It's also not clear what you mean by unique. You say unique by person but since you've added a cell for month that implies the month shoukd enter the calculation.

YOu ask for a formula but in many cases like this a Pivot Table will provide the answer without you needing to resort to formulae. In this case you'd need to create a data range of three columns. Date, Name, Value (i.e. A1, A2..etc)

Therefore upload your real workbook (or at least a cut down copy), and manually add the results you expect to see.

[tkoral]

Ali,

As always, you've got an amazing solution. One thing I realized is, I included my "unique" ID in E1, however, is it possible if I don't have the ID provided? In other words, I have truly unique IDs, such as 0060g000010eGlH, but they're in my source data. I'll end up with hundreds of them. What I was HOPING to be able to do was write a formula that saw those IDs as unique, without having to actually list them out. Does that make sense?

Thank you SO much for your help.

[tkoral]

Hi,

This is clearly a much simplified example ...

Hey Richard!

Thanks for the response. I definitely understand that a pivot table could work here, but the way that I've built some of the dependent tables and such has me a little more comfortable with a formula... if possible. That said, I'll work on creating a better source file.

You are correct that I'm trying to break out the data by rep, by month. My data source is going to continue to grow, month after month.

Thanks again for the response!!

[AliGW]

Ali,
... Does that make sense? ...

No - not really!

As Richard suggested, provide a more realistic set of data for us to work with.

[tkoral]

Hi there,

I've attached a better, more realistic example of work. Please let me know if you have more questions.

Thanks!

[AliGW]

Try this:

=SUM(--(FREQUENCY(IF($A$2:$A$33=$G2,IF($C$2:$C$33=$F$1,MATCH($B$2:$B$33,$B$2:$B$33,0))),ROW($B$2:$B$33)-ROW(B2)+1)>0))

... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

EDIT: might need to be this, actually:

=SUM(--(FREQUENCY(IF($A$2:$A$33=$G2,IF($D$2:$D$33=H$1,IF($C$2:$C$33=$F$1,MATCH($B$2:$B$33,$B$2:$B$33,0)))),ROW($B$2:$B$33)-ROW(B2)+1)>0))

Excel 2016 (Windows) 32 bit

	F	G	H	I
1	Verbal/Commit	Assigned	Jan	Feb
2		Bob	3	1
3		Susan	3	2
4		Tom	1	1
5		Todd	2	0

Sheet: Sheet1

[tkoral]

As always, this works perfectly. I canNOT thank you enough!!!!

[AliGW]

No problem.