Help with a formula

Brian R. Butler RN

New Member
Joined
Oct 6, 2006
Messages
7
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….
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

PolarBear

Board Regular
Joined
Sep 26, 2005
Messages
215
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 from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
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

Well-known Member
Joined
Jan 17, 2004
Messages
1,446

ADVERTISEMENT

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
ABCDEFG
1NameIPOPProcedure 1Procedure 2Procedure 3Procedure 4
2Dr JonesIPCVNICD
3Dr SmithIPICDCVN
4Dr JonesIPICDCvn
5Dr SmithIPPPG
6Dr JonesOPPPGICDCVN
7Dr JonesOPPPG
8Dr WessonOPPPGCVN
9Dr wessonIP
10Dr JonesIPPPGOTHERICDCVN
11Dr JonesOpICDPPG
12
13Calc CriteriaTotal
14Dr JonesIPCVN3
Sheet1


I'm, sure there must be a better way
 

Brian R. Butler RN

New Member
Joined
Oct 6, 2006
Messages
7
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

New Member
Joined
Oct 6, 2006
Messages
7

ADVERTISEMENT

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

New Member
Joined
Oct 6, 2006
Messages
7
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

New Member
Joined
Oct 6, 2006
Messages
7
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

New Member
Joined
Oct 6, 2006
Messages
7
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....
 

Forum statistics

Threads
1,141,628
Messages
5,707,499
Members
421,511
Latest member
mgroah1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top