COUNTIFS WITH DISTINCT COUNT

[TODDLL]

I am counting patients by a specific clinic with a specific visit type. I am using the COUNTIFS formula. I have discovered that some patients have had their clinic visit on a specific date documented twice, and I do not want to count the duplicate entry. There is a unique visit identifier for each visit. If the patient has the same visit ID more than once, I only want to count it once. I am not sure how to work that into the formula. In the image below, if I am counting CLINIC B that had a TYPE C visit, the count would come up as 3, when really there was only 2.

NAME	DATE	CLINIC	VISIT TYPE	VISIT ID
PT A	7/12/2020​	CLINIC B	TYPE C	123456​
PT A	7/12/2020​	CLINIC B	TYPE C	123456​
PT B	7/12/2020​	CLINIC F	TYPE G	23412​
PT C	7/14/2020​	CLINIC C	TYPE W	45623​
PT F	7/20/2020​	CLINIC B	TYPE C	895432​

 

[Eric W]

What version of Excel are you using? There are different functions available depending on your version. If you update your profile, it would help. Here's one way:

Book1
	A	B	C	D	E	F	G	H	I
1	NAME	DATE	CLINIC	VISIT TYPE	VISIT ID		Clinic	Type	Count
2	PT A	7/12/2020	CLINIC B	TYPE C	123456		Clinic B	Type C	2
3	PT A	7/12/2020	CLINIC B	TYPE C	123456
4	PT B	7/12/2020	CLINIC F	TYPE G	23412
5	PT C	7/14/2020	CLINIC C	TYPE W	45623
6	PT F	7/20/2020	CLINIC B	TYPE C	895432
Sheet2

Cell Formulas
Range	Formula
I2	I2	=SUM(SIGN(FREQUENCY(IF(C2:C6=G2,IF(D2:D6=H2,E2:E6)),E2:E6)))
Press CTRL+SHIFT+ENTER to enter array formulas.

This assumes that the Visit ID is an actual number. If you have the latest Excel 365, there's an easier way.

 

[TODDLL]

Amazing! I have no formal Excel training and have just learned along the way. I thought I was getting smarter, but guess not. This is above my skill level, but it works!! Thank you so much. Our organization is still on Office 2010 but is slowly migrating over to the cloud. I did update my profile. Thanks for the kind reminder, and thank you so much for your help. This is a great place to get help from experts.

 

[Eric W]

Oh, we're all still learning! When I first saw that formula (on a post in this forum), I had no idea what it was doing either! But I learned from the experts back then, and I'm just passing it along. Glad I could help.