Counting unique text in array

[binary101]

Hi,

Im trying to count the number of unique drugs given to patients in an excel array, I currently use =SUMPRODUCT(1/COUNTIFS(B2:B9,B2:B9)) however I have to manually shift the cells when the Pt ID changes as the number of drugs given is different for each patient. Is there a way to display the Pt ID when the value changes as well as counting the total number of drugs given to that patient?

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this. Note that the column E formula should be confirmed with Ctrl+Shift+Enter, not just Enter

21 10 21.xlsm
	A	B	C	D	E
1
2	1	Drug 2		1	4
3	1	Drug 4		3	1
4	1	Drug 3		5	3
5	1	Drug 2
6	1	Drug 2
7	1	Drug 1
8	1	Drug 1
9	1	Drug 2
10	3	Drug 3
11	5	Drug 2
12	5	Drug 4
13	5	Drug 4
14	5	Drug 3
15	5	Drug 4
16	5	Drug 3
Count Unique

Cell Formulas
Range	Formula
D2:D7	D2	=IFERROR(INDEX($A$2:$A$100,MATCH(0,INDEX(COUNTIF($D$1:D1,$A$2:$A$100)+(A$2:A$100=""),0),0)),"")
E2:E7	E2	=IF(D2="","",COUNT(1/(MATCH(D2&"|"&B$2:B$16,A$2:A$16&"|"&B$2:B$16,0)=ROW(A$2:A$16)-ROW(A$2)+1)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[binary101]

Thank you so much, I have tired it on a the actual data set, thought the formula didnt work but just took a while to go through the entire set, still trying to get my head around the nested index, match and countifs ?.

 

[Peter_SSs]

thought the formula didnt work but just took a while to go through the entire set,

Do you have very large ranges, or perhaps used whole column references in the formulas?

 

[binary101]

The data set has about 4700 rows which I specified instead of using the entire column.

 

[Peter_SSs]

Oh well, not much more you can do then I think. At least it sounds like the correct results came in the end.