Help with a formula

[Brian R. Butler RN]

Good day to you. I am Brian. I am not all that computer literate, just enough to get myself into trouble… I am having a problem with an Excel formula. I have to extract data from a form that we use on Access. I move the data into Excel then attempt to quantify it.

What I am looking for is: I have several names in column “A” (Dr. Jones, Dr. Smith and Dr. Wesson…etc). Each of them performs several procedures on patients over a month period of time. The patients are placed into two classes, Inpatient and Outpatients, column “B” and “C”, (IP and OP). There are about ten procedures to choose from, column “D”, “E”, “F”…etc. (CVN, ICD, PPG…etc.).

I want to sum all of the patients that were seen by Dr. Jones and are classified as Inpatients (IP) and had the CVN procedure (there are three columns that have procedures in them because we may do more than one procedure on a given patient). The formula I keep trying is: =countif{(A2:A23,”Jones”)and(D2:D23,”IP”)and(E2:E23,”CVN”)and(F2:F23,”CVN”) (G2:G23,”CVN”)}
I keep getting errors when I attempt to execute it. If you need more explanation to help me please contact me and I will provide the additional information.

Please help….

 

[PolarBear]

I noticed you are using curly brackets {} to enclose your formula. Was that just your typing, or is that the formula on your sheet? Excel wants you to use () regular brackets. What is the error message you get?

 

[Brian R. Butler RN]

Yes, I did use the {} to enclose the whole formula. I will get back to you on the error message. Thank you.

 

[Brian from Maui]

Good day to you. I am Brian. I am not all that computer literate, just enough to get myself into trouble… I am having a problem with an Excel formula. I have to extract data from a form that we use on Access. I move the data into Excel then attempt to quantify it.

What I am looking for is: I have several names in column “A” (Dr. Jones, Dr. Smith and Dr. Wesson…etc). Each of them performs several procedures on patients over a month period of time. The patients are placed into two classes, Inpatient and Outpatients, column “B” and “C”, (IP and OP). There are about ten procedures to choose from, column “D”, “E”, “F”…etc. (CVN, ICD, PPG…etc.).

I want to sum all of the patients that were seen by Dr. Jones and are classified as Inpatients (IP) and had the CVN procedure (there are three columns that have procedures in them because we may do more than one procedure on a given patient). The formula I keep trying is: =countif{(A2:A23,”Jones”)and(D2:D23,”IP”)and(E2:E23,”CVN”)and(F2:F23,”CVN”) (G2:G23,”CVN”)}
I keep getting errors when I attempt to execute it. If you need more explanation to help me please contact me and I will provide the additional information.

Please help…. Brian.Butler@uhhs.com

A generic multi conditional counting formula using SUMPRODUCT,

=SUMPRODUCT(--(A2:A23="Jones"),--(D2:D23="IP"),--(E2:E23="CVN"))

BTW

You probably want to delete your email address....

 

[GorD]

Try something like

=SUMPRODUCT(--(A2:A11=A14),--(B2:B11=B14),(D2:D11=C14)+(E2:E11=C14)+(F2:F11=C14)+(G2:G11=C14))

which has and conditions for col A and B and then OR conditions for the rest. I have set up for 4 op columns, you'll need to expand for 10.

Book1
	A	B	C	D	E	F	G
1	Name	IP	OP	Procedure 1	Procedure 2	Procedure 3	Procedure 4
2	Dr Jones	IP		CVN	ICD
3	Dr Smith	IP		ICD	CVN
4	Dr Jones	IP		ICD	Cvn
5	Dr Smith	IP		PPG
6	Dr Jones		OP	PPG	ICD	CVN
7	Dr Jones		OP	PPG
8	Dr Wesson		OP	PPG	CVN
9	Dr wesson	IP
10	Dr Jones	IP		PPG	OTHER	ICD	CVN
11	Dr Jones		Op	ICD	PPG
12
13	Calc Criteria			Total
14	Dr Jones	IP	CVN	3
Sheet1

I'm, sure there must be a better way

 

[Brian R. Butler RN]

Good day to you. I am Brian. I am not all that computer literate, just enough to get myself into trouble… I am having a problem with an Excel formula. I have to extract data from a form that we use on Access. I move the data into Excel then attempt to quantify it.

What I am looking for is: I have several names in column “A” (Dr. Jones, Dr. Smith and Dr. Wesson…etc). Each of them performs several procedures on patients over a month period of time. The patients are placed into two classes, Inpatient and Outpatients, column “B” and “C”, (IP and OP). There are about ten procedures to choose from, column “D”, “E”, “F”…etc. (CVN, ICD, PPG…etc.).

I want to sum all of the patients that were seen by Dr. Jones and are classified as Inpatients (IP) and had the CVN procedure (there are three columns that have procedures in them because we may do more than one procedure on a given patient). The formula I keep trying is: =countif{(A2:A23,”Jones”)and(D2:D23,”IP”)and(E2:E23,”CVN”)and(F2:F23,”CVN”) (G2:G23,”CVN”)}
I keep getting errors when I attempt to execute it. If you need more explanation to help me please contact me and I will provide the additional information.

Please help….

A generic multi conditional counting formula using SUMPRODUCT,

=SUMPRODUCT(--(A2:A23="Jones"),--(D2:D23="IP"),--(E2:E23="CVN"))

BTW

You probably want to delete your email address....

 

[Brian R. Butler RN]

Good day to you. I am Brian. I am not all that computer literate, just enough to get myself into trouble… I am having a problem with an Excel formula. I have to extract data from a form that we use on Access. I move the data into Excel then attempt to quantify it.

What I am looking for is: I have several names in column “A” (Dr. Jones, Dr. Smith and Dr. Wesson…etc). Each of them performs several procedures on patients over a month period of time. The patients are placed into two classes, Inpatient and Outpatients, column “B” and “C”, (IP and OP). There are about ten procedures to choose from, column “D”, “E”, “F”…etc. (CVN, ICD, PPG…etc.).

I want to sum all of the patients that were seen by Dr. Jones and are classified as Inpatients (IP) and had the CVN procedure (there are three columns that have procedures in them because we may do more than one procedure on a given patient). The formula I keep trying is: =countif{(A2:A23,”Jones”)and(D2:D23,”IP”)and(E2:E23,”CVN”)and(F2:F23,”CVN”) (G2:G23,”CVN”)}
I keep getting errors when I attempt to execute it. If you need more explanation to help me please contact me and I will provide the additional information.

Please help….

A generic multi conditional counting formula using SUMPRODUCT,

=SUMPRODUCT(--(A2:A23="Jones"),--(D2:D23="IP"),--(E2:E23="CVN"))

BTW

You probably want to delete your email address....

 

[Brian R. Butler RN]

Good day to you. I am Brian. I am not all that computer literate, just enough to get myself into trouble… I am having a problem with an Excel formula. I have to extract data from a form that we use on Access. I move the data into Excel then attempt to quantify it.

What I am looking for is: I have several names in column “A” (Dr. Jones, Dr. Smith and Dr. Wesson…etc). Each of them performs several procedures on patients over a month period of time. The patients are placed into two classes, Inpatient and Outpatients, column “B” and “C”, (IP and OP). There are about ten procedures to choose from, column “D”, “E”, “F”…etc. (CVN, ICD, PPG…etc.).

I want to sum all of the patients that were seen by Dr. Jones and are classified as Inpatients (IP) and had the CVN procedure (there are three columns that have procedures in them because we may do more than one procedure on a given patient). The formula I keep trying is: =countif{(A2:A23,”Jones”)and(D2:D23,”IP”)and(E2:E23,”CVN”)and(F2:F23,”CVN”) (G2:G23,”CVN”)}
I keep getting errors when I attempt to execute it. If you need more explanation to help me please contact me and I will provide the additional information.

Please help….

A generic multi conditional counting formula using SUMPRODUCT,

=SUMPRODUCT(--(A2:A23="Jones"),--(D2:D23="IP"),--(E2:E23="CVN"))

BTW

You probably want to delete your email address....

 

[Brian R. Butler RN]

Good day to you. I am Brian. I am not all that computer literate, just enough to get myself into trouble… I am having a problem with an Excel formula. I have to extract data from a form that we use on Access. I move the data into Excel then attempt to quantify it.

What I am looking for is: I have several names in column “A” (Dr. Jones, Dr. Smith and Dr. Wesson…etc). Each of them performs several procedures on patients over a month period of time. The patients are placed into two classes, Inpatient and Outpatients, column “B” and “C”, (IP and OP). There are about ten procedures to choose from, column “D”, “E”, “F”…etc. (CVN, ICD, PPG…etc.).

I want to sum all of the patients that were seen by Dr. Jones and are classified as Inpatients (IP) and had the CVN procedure (there are three columns that have procedures in them because we may do more than one procedure on a given patient). The formula I keep trying is: =countif{(A2:A23,”Jones”)and(D2:D23,”IP”)and(E2:E23,”CVN”)and(F2:F23,”CVN”) (G2:G23,”CVN”)}
I keep getting errors when I attempt to execute it. If you need more explanation to help me please contact me and I will provide the additional information.

Please help….

A generic multi conditional counting formula using SUMPRODUCT,

=SUMPRODUCT(--(A2:A23="Jones"),--(D2:D23="IP"),--(E2:E23="CVN"))

BTW

You probably want to delete your email address....

 

[Brian R. Butler RN]

Good day to you. I am Brian. I am not all that computer literate, just enough to get myself into trouble… I am having a problem with an Excel formula. I have to extract data from a form that we use on Access. I move the data into Excel then attempt to quantify it.

What I am looking for is: I have several names in column “A” (Dr. Jones, Dr. Smith and Dr. Wesson…etc). Each of them performs several procedures on patients over a month period of time. The patients are placed into two classes, Inpatient and Outpatients, column “B” and “C”, (IP and OP). There are about ten procedures to choose from, column “D”, “E”, “F”…etc. (CVN, ICD, PPG…etc.).

I want to sum all of the patients that were seen by Dr. Jones and are classified as Inpatients (IP) and had the CVN procedure (there are three columns that have procedures in them because we may do more than one procedure on a given patient). The formula I keep trying is: =countif{(A2:A23,”Jones”)and(D2:D23,”IP”)and(E2:E23,”CVN”)and(F2:F23,”CVN”) (G2:G23,”CVN”)}
I keep getting errors when I attempt to execute it. If you need more explanation to help me please contact me and I will provide the additional information.

Please help….

A generic multi conditional counting formula using SUMPRODUCT,

=SUMPRODUCT(--(A2:A23="Jones"),--(D2:D23="IP"),--(E2:E23="CVN"))

BTW

You probably want to delete your email address....