To setup the scenario, I have 10 doctors from 15 clinics being tracked on one
spreadsheet. There are three columns I want to count from: Doctor, Clinic,
Gender. I would like to create logic statement to count the total number of
patients a specific doctor sees in relation to a specific clinic. Also how
many patients he sees from a region such as the northern clinics (let's say
clinic A,B,C all designated within the "Clinic" column) and how many of the
total people he sees are from his own clinic.

Relating to gender, I would like to count how many of each gender were seen
at each clinic and how many of each gender were seen for a region.

I am sure that the answer is really a variation of a nestled statement to
answer all of these, but I d not know how to create it. I will list a sample
of the columns below:

Gender Clinic Doctor
Male North A
Female North A
Male South B
Male North B
Female South C
Female East A
Male North A

Total North seen by Doctor A:
Total Non-North seen by Doctor A:
Total North and South, but not East seen by Doctor A:
Total Male seen by North:
Total Male seen by Non-North:
Total Male seen by Doctor A:
Total Male from North seen by Doctor A:
Total Male from Non-North seen by Doctor A:
Total North seen by other than Doctor A:

I'm sure you get my point. Thank you for your help.

See answer in your other post - please do not multi-post (although
this has more detail).

Pete

On Jan 15, 9:43*am, Scott wrote:
To setup the scenario, I have 10 doctors from 15 clinics being tracked on one
spreadsheet. *There are three columns I want to count from: Doctor, Clinic,
Gender. I would like to create logic statement to count the total number of
patients a specific doctor sees in relation to a specific clinic. *Also how
many patients he sees from a region such as the northern clinics (let's say
clinic A,B,C all designated within the "Clinic" column) and how many of the
total people he sees are from his own clinic. *

Relating to gender, I would like to count how many of each gender were seen
at each clinic and how many of each gender were seen for a region.

I am sure that the answer is really a variation of a nestled statement to
answer all of these, but I d not know how to create it. I will list a sample
of the columns below:

Gender * * * * * * Clinic * * * * * * *Doctor

Male * * * * * * * North * * * * * * *A
Female * * * * * North * * * * * * *A
Male * * * * * * * South * * * * * * *B
Male * * * * * * * North * * * * * * *B
Female * * * * * South * * * * * * *C
Female * * * * * East * * * * * * * *A
Male * * * * * * * North * * * * * * *A

Total North seen by Doctor A:
Total Non-North seen by Doctor A:
Total North and South, but not East seen by Doctor A:
Total Male seen by North:
Total Male seen by Non-North:
Total Male seen by Doctor A:
Total Male from North seen by Doctor A:
Total Male from Non-North seen by Doctor A:
Total North seen by other than Doctor A:

I'm sure you get my point. Thank you for your help.

YES, I thought about the lack of detail in the other post and thought I
would clarify with this one. Thank you


"Pete_UK" wrote:

See answer in your other post - please do not multi-post (although
this has more detail).

Pete

On Jan 15, 9:43 am, Scott wrote:
To setup the scenario, I have 10 doctors from 15 clinics being tracked on one
spreadsheet. There are three columns I want to count from: Doctor, Clinic,
Gender. I would like to create logic statement to count the total number of
patients a specific doctor sees in relation to a specific clinic. Also how
many patients he sees from a region such as the northern clinics (let's say
clinic A,B,C all designated within the "Clinic" column) and how many of the
total people he sees are from his own clinic.

Relating to gender, I would like to count how many of each gender were seen
at each clinic and how many of each gender were seen for a region.

I am sure that the answer is really a variation of a nestled statement to
answer all of these, but I d not know how to create it. I will list a sample
of the columns below:

Gender Clinic Doctor

Male North A
Female North A
Male South B
Male North B
Female South C
Female East A
Male North A

Total North seen by Doctor A:
Total Non-North seen by Doctor A:
Total North and South, but not East seen by Doctor A:
Total Male seen by North:
Total Male seen by Non-North:
Total Male seen by Doctor A:
Total Male from North seen by Doctor A:
Total Male from Non-North seen by Doctor A:
Total North seen by other than Doctor A:

I'm sure you get my point. Thank you for your help.

I read your post:

Assume that your main data is in column A to C, starting on row 2. Use
cells D1, E1 and F1 to enable you to specify the doctor's name, the
county and the gender respectively, and then put this formula in G1:

=SUMPRODUCT(($A$2:$A$1000=D1)*($B$2:$B$1000=E1)*($ C$2:$C$1000=F1))

Just change the values in D1, E1 and F1 to get a different result. You
can also copy the formula down to count values in D2:F2 etc.

But I forgot to mention that the information is on one sheet and the totals
are on another sheet. Also, I am fairly new to this, so I am not sure if I
am doing this correctly, so here is what I put in:

=SUMPRODUCT(('Monthly USAGE REPORT'!J4:J65536="Johnson*")*('Monthly USAGE
REPORT'!H4:H65536="North*"))

But I did not get the expected results. I used the high lite option when
picking the cells and the program automatically gave me the ! instead of the
$ that you had listed?

To setup the scenario, I have 10 doctors from 15 clinics being tracked on one
spreadsheet. There are three columns I want to count from: Doctor, Clinic,
Gender. I would like to create logic statement to count the total number of
patients a specific doctor sees in relation to a specific clinic. Also how
many patients he sees from a region such as the northern clinics (let's say
clinic A,B,C all designated within the "Clinic" column) and how many of the
total people he sees are from his own clinic.

Relating to gender, I would like to count how many of each gender were seen
at each clinic and how many of each gender were seen for a region.

I am sure that the answer is really a variation of a nestled statement to
answer all of these, but I d not know how to create it. I will list a sample
of the columns below:

Gender Clinic Doctor

Male North-A Johnsonstien
Female North-A Johnsonstien
Male South Billing
Male North -B Billing
Female South Crestofen
Female East Johnsonstien
Male North-A Johnsonstien

Total North seen by Doctor Johnsonstien:
Total Non-North seen by Doctor Johnsonstien:
Total North and South, but not East seen by Doctor Johnsonstien:
Total Male seen by North:
Total Male seen by Non-North:
Total Male seen by Doctor Johnsonstien:
Total Male from North seen by Doctor Johnsonstien:
Total Male from Non-North seen by Doctor Johnsonstien:
Total North seen by other than Doctor Johnsonstien:

You only need the $ symbols if you are going to copy the formula down
- the ! is used to indicate a sheet name, which you need in this case.
However, you seem to be trying to use an asterisk as a wildcard, and
that won't work in this case - try it like this:

=SUMPRODUCT((LEFT('Monthly USAGE REPORT'!
J4:J65536,7)="Johnson")*(LEFT('Monthly USAGE REPORT'!
H4:H65536,5)="North"))

Do you really need to use almost a complete column?

It would be better to put Johnson and North in other cells on the same
sheet and refer to those cells in the formula.

Hope this helps.

Pete


On Jan 15, 10:37*am, Scott wrote:
I read your post:

Assume that your main data is in column A to C, starting on row 2. Use
cells D1, E1 and F1 to enable you to specify the doctor's name, the
county and the gender respectively, and then put this formula in G1:

=SUMPRODUCT(($A$2:$A$1000=D1)*($B$2:$B$1000=E1)*($ C$2:$C$1000=F1))

Just change the values in D1, E1 and F1 to get a different result. You
can also copy the formula down to count values in D2:F2 etc.

But I forgot to mention that the information is on one sheet and the totals
are on another sheet. *Also, I am fairly new to this, so I am not sure if I
am doing this correctly, so here is what I put in:

=SUMPRODUCT(('Monthly USAGE REPORT'!J4:J65536="Johnson*")*('Monthly USAGE
REPORT'!H4:H65536="North*"))

But I did not get the expected results. *I used the high lite option when
picking the cells and the program automatically gave me the ! instead of the
$ that you had listed?



To setup the scenario, I have 10 doctors from 15 clinics being tracked on one
spreadsheet. *There are three columns I want to count from: Doctor, Clinic,
Gender. I would like to create logic statement to count the total number of
patients a specific doctor sees in relation to a specific clinic. *Also how
many patients he sees from a region such as the northern clinics (let's say
clinic A,B,C all designated within the "Clinic" column) and how many of the
total people he sees are from his own clinic. *

Relating to gender, I would like to count how many of each gender were seen
at each clinic and how many of each gender were seen for a region.

I am sure that the answer is really a variation of a nestled statement to
answer all of these, but I d not know how to create it. I will list a sample
of the columns below:

Gender * * * * * * Clinic * * * * * * *Doctor

Male * * * * * * * North-A * * * *Johnsonstien
Female * * * * * North-A * * * *Johnsonstien
Male * * * * * * * South * * * * * *Billing
Male * * * * * * * North -B * * * Billing
Female * * * * * South * * * * * *Crestofen
Female * * * * * East * * * * * * *Johnsonstien
Male * * * * * * * North-A * * * *Johnsonstien

Total North seen by Doctor *Johnsonstien:
Total Non-North seen by Doctor *Johnsonstien:
Total North and South, but not East seen by Doctor *Johnsonstien:
Total Male seen by North:
Total Male seen by Non-North:
Total Male seen by Doctor *Johnsonstien:
Total Male from North seen by Doctor *Johnsonstien:
Total Male from Non-North seen by Doctor *Johnsonstien:
Total North seen by other than Doctor *Johnsonstien:- Hide quoted text -

- Show quoted text -

KK, tried that and it didn't really give me any total numbers seen? not sure
how to better explain it then what I have included?

"Pete_UK" wrote:

You only need the $ symbols if you are going to copy the formula down
- the ! is used to indicate a sheet name, which you need in this case.
However, you seem to be trying to use an asterisk as a wildcard, and
that won't work in this case - try it like this:

=SUMPRODUCT((LEFT('Monthly USAGE REPORT'!
J4:J65536,7)="Johnson")*(LEFT('Monthly USAGE REPORT'!
H4:H65536,5)="North"))

Do you really need to use almost a complete column?

It would be better to put Johnson and North in other cells on the same
sheet and refer to those cells in the formula.

Hope this helps.

Pete


On Jan 15, 10:37 am, Scott wrote:
I read your post:

Assume that your main data is in column A to C, starting on row 2. Use
cells D1, E1 and F1 to enable you to specify the doctor's name, the
county and the gender respectively, and then put this formula in G1:

=SUMPRODUCT(($A$2:$A$1000=D1)*($B$2:$B$1000=E1)*($ C$2:$C$1000=F1))

Just change the values in D1, E1 and F1 to get a different result. You
can also copy the formula down to count values in D2:F2 etc.

But I forgot to mention that the information is on one sheet and the totals
are on another sheet. Also, I am fairly new to this, so I am not sure if I
am doing this correctly, so here is what I put in:

=SUMPRODUCT(('Monthly USAGE REPORT'!J4:J65536="Johnson*")*('Monthly USAGE
REPORT'!H4:H65536="North*"))

But I did not get the expected results. I used the high lite option when
picking the cells and the program automatically gave me the ! instead of the
$ that you had listed?



To setup the scenario, I have 10 doctors from 15 clinics being tracked on one
spreadsheet. There are three columns I want to count from: Doctor, Clinic,
Gender. I would like to create logic statement to count the total number of
patients a specific doctor sees in relation to a specific clinic. Also how
many patients he sees from a region such as the northern clinics (let's say
clinic A,B,C all designated within the "Clinic" column) and how many of the
total people he sees are from his own clinic.

Relating to gender, I would like to count how many of each gender were seen
at each clinic and how many of each gender were seen for a region.

I am sure that the answer is really a variation of a nestled statement to
answer all of these, but I d not know how to create it. I will list a sample
of the columns below:

Gender Clinic Doctor

Male North-A Johnsonstien
Female North-A Johnsonstien
Male South Billing
Male North -B Billing
Female South Crestofen
Female East Johnsonstien
Male North-A Johnsonstien

Total North seen by Doctor Johnsonstien:
Total Non-North seen by Doctor Johnsonstien:
Total North and South, but not East seen by Doctor Johnsonstien:
Total Male seen by North:
Total Male seen by Non-North:
Total Male seen by Doctor Johnsonstien:
Total Male from North seen by Doctor Johnsonstien:
Total Male from Non-North seen by Doctor Johnsonstien:
Total North seen by other than Doctor Johnsonstien:- Hide quoted text -

- Show quoted text -