Count Unique Values IF a Column Contains Data

[sdowney]

So I've been looking through many posts on this forum as well as other sites like exceljet and mrexcel, but I could not find an answer to my below issue. If I've overlooked a thread that you think would be helpful, then please let me know!

I have a set of columns with data in the rows below the columns. I am attempting to count the unique values in column A by the three various care levels that are in column D. I've tried a few different formulas including countif, sumif, sumproduct with a 1/countif, frequency, etc but I just can't put one together to get me the info.

I am using Office 2007 and in the below data, I would basically want to know how many units are AL, IL, and DM. It should be 2 AL, 3 IL, 3 DM. So I would want a formula that does IF column D contains AL count how many unique values are in column A.

Unit	Type		Privacy	Care Level
1	alstudio   	PRI	AL
2	al2br		SPA	AL
2	al2br		SPB	AL
3	al1br		PRI	DM
4	al1br		SPA	DM
4	al1br		SPB	DM
5	al2br		SPA	IL
5	al2br		SPB	IL
6	alstudio   	PRI	DM
7	al2br		PRI	IL
9	al1br		PRI	IL

Any help is greatly appreciated, and if there's additional info that I didn't provide please let me know! Thanks!

[David A Coop]

Welcome SDowney,

Assuming you have AL etc in F1:F3, and the data is in A1:D12, then in call G2

=SUMPRODUCT(1/COUNTIF($A$2:$A$12,$A$2:$A$12),--($D$2:$D$12=F1)) and copy down.

This gives the result you are looking for.

I hope this helps, please let me know!

Regards,

David

If this has been helpful
- Please click on the *Add Reputation button at the bottom left.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[sdowney]

Thank you for your help, David. The formula works great! But I do have one issue, that if the there is a blank in column A the formula gets an error. Is there a way around that?

[JBeaucaire]

The formula shown in H2 is
=SUM(INDEX(($D$1:$D$12=F2)*(MATCH($A$1:$A$12,$A$1:$A$12,0)=ROW($A$1:$A$12)),0))

Data Range

	A	B	C	D	E	F	G	H
1	Unit	Type	Privacy	Care Level
2	1	alstudio	PRI	AL		AL	2	=SUM(INDEX(($D$1:$D$12=F2)*(MATCH($A$1:$A$12,$A$1:$A$12,0)=ROW($A$1:$A$12)),0))
3	2	al2br	SPA	AL		DM	3
4	2	al2br	SPB	AL		IL	3
5	3	al1br	PRI	DM
6	4	al1br	SPA	DM
7	4	al1br	SPB	DM
8	5	al2br	SPA	IL
9	5	al2br	SPB	IL
10	6	alstudio	PRI	DM
11	7	al2br	PRI	IL
12	9	al1br	PRI	IL

[JeteMc]

With some modification/transformation Jerry's formula will work when there is a blank in column A.
Using the setup in post #3, select cell G2 and paste the following array entered formula* into the formula bar:

=SUM(INDEX(($D$1:$D$12=F2)*(IFERROR(MATCH($A$1:$A$12,$A$1:$A$12,0),0)=ROW($A$1:$A$12)),0))

*After pasting the formula into the formula bar, activate the formula by simultaneously pressing Ctrl, Shift and Enter. You will then be able to copy the formula down.
Let us know if you have any questions.