I am trying to get a total count from two seperate worksheets.

ORLog sheet1
SPLog sheet2

on my summary sheet(Daily), row 2 starting with C contains the dates of the
month across 1 2 3, etc. formated for d......and column B starting at B3
has abbrevation names for doctors. ORLog and SPLog have the same formats and
column headers. Trying to count for each doctor, from colA(date) in both
ORLog and SPLog....colE has the doctors abbreviated name in both sheets also.
My formula is below and have been working on for several hours and am still
getting 0's


=SUMPRODUCT((ORLog!$A$2:$A$65535=C$2)*(ORLog!$E$2: $E$65535=$B2)*(SPLog!$A$2:$A$65535=C$2)*(SPLog!$E$ 2:$E$65535=$B2))

hoping someone can help me????

If you have dates in column A and days of the month in row 2, then you
are not really comparing like-with-like. Also, do you really need to
go down to row 65,535? - that's a lot of rows of data !!

Anyway, you could try something like this:

=SUMPRODUCT((DAY(ORLog!$A$3:$A$65535)=C$2)*(ORLog! $E$3:$E$65535=$B3))
+ SUMPRODUCT((DAY(SPLog!$A$3*:$A$65535)=C$2)*(SPLog! $E$3:$E$65535=
$B3))

I've adjusted the start or the ranges to row 3, as you appear to have
headings above this. Copy the formula across and down as required.

Hope this helps.

Pete

On Jun 24, 9:58*pm, Tasha wrote:
I am trying to get a total count from two seperate worksheets. *

ORLog *sheet1
SPLog * sheet2

on my summary sheet(Daily), row 2 starting with C contains the dates of the
month across * 1 2 3, etc. *formated for d......and column B starting at B3
has abbrevation names for doctors. *ORLog and SPLog have the same formats and
column headers. *Trying to count for each doctor, from colA(date) in both
ORLog and SPLog....colE has the doctors abbreviated name in both sheets also.
*My formula is below and have been working on for several hours and am still
getting 0's

=SUMPRODUCT((ORLog!$A$2:$A$65535=C$2)*(ORLog!$E$2: $E$65535=$B2)*(SPLog!$A$2*:$A$65535=C$2)*(SPLog!$E $2:$E$65535=$B2))

hoping someone can help me????

Thanks so much for your reply.....for some reason I put your formula in, but
when it tries to calculate, it sits there for a very long time, then stops
and locks up at 45% calculation. Maybe if I kind of give you an idea of my
summary sheet...
date 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
etc.... Total
DrA
DrB
DrC
DrD
DrE
DrF

The dates above are formatted to only show the number of the day, but is
actually the date. ex 06/01/2008. Then on the ORLog and SPLog, is the data
shown as below: same data format, same columns, different surgical procedures
on each page. I am using them to summarize on two other sheets as well for
the different procedures, but on this summary sheet(Daily), I need to combine
the information to get the total number of patients per doctor. So for my
example below, Dr A would have 1 for 6-1 and 1 for 6-1, DrD would have 1 for
6-2 and so on. The dates here are also the actual dates, but are formated
for 'd'.
Date Acct# Room PatName Doc
1 200001 INPT Joe Schmoe DrA
2 200002 OPS Jane Doe DrD
3 200003 INPT John Moss DrB
3 200005 INPT Doris Blu DrA

Oh, and the reason I used A2:A65535 was because I couldn't get it to work
with a named range for any information that would be added to that column,
the ORLog and SPLog are linked to a document that is updated each time I open
the file. I tried also doing a dynamic range, but that didn't work for the
SPLog when I added it to my formula...only the ORLog. Hope I have explained
this a little better, is a little complex. Thanks again for your help!!!

"Pete_UK" wrote:

If you have dates in column A and days of the month in row 2, then you
are not really comparing like-with-like. Also, do you really need to
go down to row 65,535? - that's a lot of rows of data !!

Anyway, you could try something like this:

=SUMPRODUCT((DAY(ORLog!$A$3:$A$65535)=C$2)*(ORLog! $E$3:$E$65535=$B3))
+ SUMPRODUCT((DAY(SPLog!$A$3Â*:$A$65535)=C$2)*(SPLog !$E$3:$E$65535=
$B3))

I've adjusted the start or the ranges to row 3, as you appear to have
headings above this. Copy the formula across and down as required.

Hope this helps.

Pete

On Jun 24, 9:58 pm, Tasha wrote:
I am trying to get a total count from two seperate worksheets.

ORLog sheet1
SPLog sheet2

on my summary sheet(Daily), row 2 starting with C contains the dates of the
month across 1 2 3, etc. formated for d......and column B starting at B3
has abbrevation names for doctors. ORLog and SPLog have the same formats and
column headers. Trying to count for each doctor, from colA(date) in both
ORLog and SPLog....colE has the doctors abbreviated name in both sheets also.
My formula is below and have been working on for several hours and am still
getting 0's

=SUMPRODUCT((ORLog!$A$2:$A$65535=C$2)*(ORLog!$E$2: $E$65535=$B2)*(SPLog!$A$2Â*:$A$65535=C$2)*(SPLog!$ E$2:$E$65535=$B2))

hoping someone can help me????

Okay, it becomes a bit simpler:

=SUMPRODUCT((ORLog!$A$3:$A$65535=C$2)*(ORLog!$E$3: $E$65535=$B3)) +
SUMPRODUCT((SPLog!$A$3:$A$65535=C$2)*(SPLog!$E$3:$ E$65535=$B3))

as you are now comparing dates directly. For testing purposes, I would
suggest you reduce those 65535 to say 100 - it will speed things up
considerably - highlight the cell(s) do Edit | Replace (or CTRL-H)
and :

Find what $65535
Replace with $100
Click Replace All.

Then reverse this if you are satisfied it is working.

Hope this helps.

Pete

On Jun 25, 2:16*pm, Tasha wrote:
Thanks so much for your reply.....for some reason I put your formula in, but
when it tries to calculate, it sits there for a very long time, then stops
and locks up at 45% calculation. *Maybe if I *kind of give you an idea of my
summary sheet...
* * * * date *1 *2 *3 *4 *5 *6 *7 *8 *9 *10 11 12 13 14 15 16 17 18 19
etc.... * * Total
DrA
DrB
DrC
DrD
DrE
DrF

The dates above are formatted to only show the number of the day, but is
actually the date. *ex 06/01/2008. *Then on the ORLog and SPLog, is the data
shown as below: same data format, same columns, different surgical procedures
on each page. *I am using them to summarize on two other sheets as well for
the different procedures, but on this summary sheet(Daily), I need to combine
the information to get the total number of patients per doctor. *So for my
example below, Dr A would have 1 for 6-1 and 1 for 6-1, DrD would have 1 for
6-2 and so on. *The dates here are also the actual dates, but are formated
for 'd'.
Date *Acct# * * Room * * PatName * * Doc
1 * * * 200001 * *INPT * *Joe Schmoe *DrA
2 * * * 200002 * *OPS * * Jane Doe * * *DrD
3 * * * 200003 * *INPT * *John Moss * * DrB
3 * * * 200005 * *INPT * *Doris Blu * * * DrA

Oh, and the reason I used A2:A65535 was because I couldn't get it to work
with a named range for any information that would be added to that column,
the ORLog and SPLog are linked to a document that is updated each time I open
the file. *I tried also doing a dynamic range, but that didn't work for the
SPLog when I added it to my formula...only the ORLog. Hope I have explained
this a little better, is a little complex. *Thanks again for your help!!!



"Pete_UK" wrote:
If you have dates in column A and days of the month in row 2, then you
are not really comparing like-with-like. Also, do you really need to
go down to row 65,535? - that's a lot of rows of data !!

Anyway, you could try something like this:

=SUMPRODUCT((DAY(ORLog!$A$3:$A$65535)=C$2)*(ORLog! $E$3:$E$65535=$B3))
+ SUMPRODUCT((DAY(SPLog!$A$3*:$A$65535)=C$2)*(SPLog! $E$3:$E$65535=
$B3))

I've adjusted the start or the ranges to row 3, as you appear to have
headings above this. Copy the formula across and down as required.

Hope this helps.

Pete

On Jun 24, 9:58 pm, Tasha wrote:
I am trying to get a total count from two seperate worksheets. *

ORLog *sheet1
SPLog * sheet2

on my summary sheet(Daily), row 2 starting with C contains the dates of the
month across * 1 2 3, etc. *formated for d......and column B starting at B3
has abbrevation names for doctors. *ORLog and SPLog have the same formats and
column headers. *Trying to count for each doctor, from colA(date) in both
ORLog and SPLog....colE has the doctors abbreviated name in both sheets also.
*My formula is below and have been working on for several hours and am still
getting 0's

=SUMPRODUCT((ORLog!$A$2:$A$65535=C$2)*(ORLog!$E$2: $E$65535=$B2)*(SPLog!$A$2**:$A$65535=C$2)*(SPLog!$ E$2:$E$65535=$B2))

hoping someone can help me????- Hide quoted text -

- Show quoted text -

that worked!!! I had tried something similar to that formula, I just didn't
put )) at the end of each... Thanks so much !!!!

"Pete_UK" wrote:

Okay, it becomes a bit simpler:

=SUMPRODUCT((ORLog!$A$3:$A$65535=C$2)*(ORLog!$E$3: $E$65535=$B3)) +
SUMPRODUCT((SPLog!$A$3:$A$65535=C$2)*(SPLog!$E$3:$ E$65535=$B3))

as you are now comparing dates directly. For testing purposes, I would
suggest you reduce those 65535 to say 100 - it will speed things up
considerably - highlight the cell(s) do Edit | Replace (or CTRL-H)
and :

Find what $65535
Replace with $100
Click Replace All.

Then reverse this if you are satisfied it is working.

Hope this helps.

Pete

On Jun 25, 2:16 pm, Tasha wrote:
Thanks so much for your reply.....for some reason I put your formula in, but
when it tries to calculate, it sits there for a very long time, then stops
and locks up at 45% calculation. Maybe if I kind of give you an idea of my
summary sheet...
date 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
etc.... Total
DrA
DrB
DrC
DrD
DrE
DrF

The dates above are formatted to only show the number of the day, but is
actually the date. ex 06/01/2008. Then on the ORLog and SPLog, is the data
shown as below: same data format, same columns, different surgical procedures
on each page. I am using them to summarize on two other sheets as well for
the different procedures, but on this summary sheet(Daily), I need to combine
the information to get the total number of patients per doctor. So for my
example below, Dr A would have 1 for 6-1 and 1 for 6-1, DrD would have 1 for
6-2 and so on. The dates here are also the actual dates, but are formated
for 'd'.
Date Acct# Room PatName Doc
1 200001 INPT Joe Schmoe DrA
2 200002 OPS Jane Doe DrD
3 200003 INPT John Moss DrB
3 200005 INPT Doris Blu DrA

Oh, and the reason I used A2:A65535 was because I couldn't get it to work
with a named range for any information that would be added to that column,
the ORLog and SPLog are linked to a document that is updated each time I open
the file. I tried also doing a dynamic range, but that didn't work for the
SPLog when I added it to my formula...only the ORLog. Hope I have explained
this a little better, is a little complex. Thanks again for your help!!!



"Pete_UK" wrote:
If you have dates in column A and days of the month in row 2, then you
are not really comparing like-with-like. Also, do you really need to
go down to row 65,535? - that's a lot of rows of data !!

Anyway, you could try something like this:

=SUMPRODUCT((DAY(ORLog!$A$3:$A$65535)=C$2)*(ORLog! $E$3:$E$65535=$B3))
+ SUMPRODUCT((DAY(SPLog!$A$3Â*:$A$65535)=C$2)*(SPLog !$E$3:$E$65535=
$B3))

I've adjusted the start or the ranges to row 3, as you appear to have
headings above this. Copy the formula across and down as required.

Hope this helps.

Pete

On Jun 24, 9:58 pm, Tasha wrote:
I am trying to get a total count from two seperate worksheets.

ORLog sheet1
SPLog sheet2

on my summary sheet(Daily), row 2 starting with C contains the dates of the
month across 1 2 3, etc. formated for d......and column B starting at B3
has abbrevation names for doctors. ORLog and SPLog have the same formats and
column headers. Trying to count for each doctor, from colA(date) in both
ORLog and SPLog....colE has the doctors abbreviated name in both sheets also.
My formula is below and have been working on for several hours and am still
getting 0's

=SUMPRODUCT((ORLog!$A$2:$A$65535=C$2)*(ORLog!$E$2: $E$65535=$B2)*(SPLog!$A$2Â*Â*:$A$65535=C$2)*(SPLog !$E$2:$E$65535=$B2))

hoping someone can help me????- Hide quoted text -

- Show quoted text -

You're welcome, Tasha - thanks for feeding back.

Pete

On Jun 25, 4:28*pm, Tasha wrote:
that worked!!! I had tried something similar to that formula, I just didn't
put )) at the end of each... *Thanks so much !!!!

Pete, I'm getting correct totals on all my doctors but one....any ideas why
it would be doing this? I retyped the doctors name abbreviation it should be
pulling by and checked the logs it's pulling from and they are both the same,
and are using same formulas the rest of the summary sheet are using? I'm at
a loss...????



"Pete_UK" wrote:

Okay, it becomes a bit simpler:

=SUMPRODUCT((ORLog!$A$3:$A$65535=C$2)*(ORLog!$E$3: $E$65535=$B3)) +
SUMPRODUCT((SPLog!$A$3:$A$65535=C$2)*(SPLog!$E$3:$ E$65535=$B3))

as you are now comparing dates directly. For testing purposes, I would
suggest you reduce those 65535 to say 100 - it will speed things up
considerably - highlight the cell(s) do Edit | Replace (or CTRL-H)
and :

Find what $65535
Replace with $100
Click Replace All.

Then reverse this if you are satisfied it is working.

Hope this helps.

Pete

On Jun 25, 2:16 pm, Tasha wrote:
Thanks so much for your reply.....for some reason I put your formula in, but
when it tries to calculate, it sits there for a very long time, then stops
and locks up at 45% calculation. Maybe if I kind of give you an idea of my
summary sheet...
date 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
etc.... Total
DrA
DrB
DrC
DrD
DrE
DrF

The dates above are formatted to only show the number of the day, but is
actually the date. ex 06/01/2008. Then on the ORLog and SPLog, is the data
shown as below: same data format, same columns, different surgical procedures
on each page. I am using them to summarize on two other sheets as well for
the different procedures, but on this summary sheet(Daily), I need to combine
the information to get the total number of patients per doctor. So for my
example below, Dr A would have 1 for 6-1 and 1 for 6-1, DrD would have 1 for
6-2 and so on. The dates here are also the actual dates, but are formated
for 'd'.
Date Acct# Room PatName Doc
1 200001 INPT Joe Schmoe DrA
2 200002 OPS Jane Doe DrD
3 200003 INPT John Moss DrB
3 200005 INPT Doris Blu DrA

Oh, and the reason I used A2:A65535 was because I couldn't get it to work
with a named range for any information that would be added to that column,
the ORLog and SPLog are linked to a document that is updated each time I open
the file. I tried also doing a dynamic range, but that didn't work for the
SPLog when I added it to my formula...only the ORLog. Hope I have explained
this a little better, is a little complex. Thanks again for your help!!!



"Pete_UK" wrote:
If you have dates in column A and days of the month in row 2, then you
are not really comparing like-with-like. Also, do you really need to
go down to row 65,535? - that's a lot of rows of data !!

Anyway, you could try something like this:

=SUMPRODUCT((DAY(ORLog!$A$3:$A$65535)=C$2)*(ORLog! $E$3:$E$65535=$B3))
+ SUMPRODUCT((DAY(SPLog!$A$3Â*:$A$65535)=C$2)*(SPLog !$E$3:$E$65535=
$B3))

I've adjusted the start or the ranges to row 3, as you appear to have
headings above this. Copy the formula across and down as required.

Hope this helps.

Pete

On Jun 24, 9:58 pm, Tasha wrote:
I am trying to get a total count from two seperate worksheets.

ORLog sheet1
SPLog sheet2

on my summary sheet(Daily), row 2 starting with C contains the dates of the
month across 1 2 3, etc. formated for d......and column B starting at B3
has abbrevation names for doctors. ORLog and SPLog have the same formats and
column headers. Trying to count for each doctor, from colA(date) in both
ORLog and SPLog....colE has the doctors abbreviated name in both sheets also.
My formula is below and have been working on for several hours and am still
getting 0's

=SUMPRODUCT((ORLog!$A$2:$A$65535=C$2)*(ORLog!$E$2: $E$65535=$B2)*(SPLog!$A$2Â*Â*:$A$65535=C$2)*(SPLog !$E$2:$E$65535=$B2))

hoping someone can help me????- Hide quoted text -

- Show quoted text -

Perhaps you have a space (or more than one) at the end of the doctor's
initials in the summary sheet. Or if you still have the shortened
range of 100 rows in your formula, perhaps his data is outside this
range in the Log sheets.

Hope this helps.

Pete

On Jun 25, 7:36*pm, Tasha wrote:
Pete, I'm getting correct totals on all my doctors but one....any ideas why
it would be doing this? *I retyped the doctors name abbreviation it should be
pulling by and checked the logs it's pulling from and they are both the same,
and are using same formulas the rest of the summary sheet are using? *I'm at
a loss...????



"Pete_UK" wrote:
Okay, it becomes a bit simpler:

=SUMPRODUCT((ORLog!$A$3:$A$65535=C$2)*(ORLog!$E$3: $E$65535=$B3)) +
SUMPRODUCT((SPLog!$A$3:$A$65535=C$2)*(SPLog!$E$3:$ E$65535=$B3))

as you are now comparing dates directly. For testing purposes, I would
suggest you reduce those 65535 to say 100 - it will speed things up
considerably - highlight the cell(s) do Edit | Replace (or CTRL-H)
and :

Find what * * * * * $65535
Replace with * * *$100
Click Replace All.

Then reverse this if you are satisfied it is working.

Hope this helps.

Pete

On Jun 25, 2:16 pm, Tasha wrote:
Thanks so much for your reply.....for some reason I put your formula in, but
when it tries to calculate, it sits there for a very long time, then stops
and locks up at 45% calculation. *Maybe if I *kind of give you an idea of my
summary sheet...
* * * * date *1 *2 *3 *4 *5 *6 *7 *8 *9 *10 11 12 13 14 15 16 17 18 19
etc.... * * Total
DrA
DrB
DrC
DrD
DrE
DrF

The dates above are formatted to only show the number of the day, but is
actually the date. *ex 06/01/2008. *Then on the ORLog and SPLog, is the data
shown as below: same data format, same columns, different surgical procedures
on each page. *I am using them to summarize on two other sheets as well for
the different procedures, but on this summary sheet(Daily), I need to combine
the information to get the total number of patients per doctor. *So for my
example below, Dr A would have 1 for 6-1 and 1 for 6-1, DrD would have 1 for
6-2 and so on. *The dates here are also the actual dates, but are formated
for 'd'.
Date *Acct# * * Room * * PatName * * Doc
1 * * * 200001 * *INPT * *Joe Schmoe *DrA
2 * * * 200002 * *OPS * * Jane Doe * * *DrD
3 * * * 200003 * *INPT * *John Moss * * DrB
3 * * * 200005 * *INPT * *Doris Blu * * * DrA

Oh, and the reason I used A2:A65535 was because I couldn't get it to work
with a named range for any information that would be added to that column,
the ORLog and SPLog are linked to a document that is updated each time I open
the file. *I tried also doing a dynamic range, but that didn't work for the
SPLog when I added it to my formula...only the ORLog. Hope I have explained
this a little better, is a little complex. *Thanks again for your help!!!

"Pete_UK" wrote:
If you have dates in column A and days of the month in row 2, then you
are not really comparing like-with-like. Also, do you really need to
go down to row 65,535? - that's a lot of rows of data !!

Anyway, you could try something like this:

=SUMPRODUCT((DAY(ORLog!$A$3:$A$65535)=C$2)*(ORLog! $E$3:$E$65535=$B3))
+ SUMPRODUCT((DAY(SPLog!$A$3*:$A$65535)=C$2)*(SPLog! $E$3:$E$65535=
$B3))

I've adjusted the start or the ranges to row 3, as you appear to have
headings above this. Copy the formula across and down as required.

Hope this helps.

Pete

On Jun 24, 9:58 pm, Tasha wrote:
I am trying to get a total count from two seperate worksheets. *

ORLog *sheet1
SPLog * sheet2

on my summary sheet(Daily), row 2 starting with C contains the dates of the
month across * 1 2 3, etc. *formated for d......and column B starting at B3
has abbrevation names for doctors. *ORLog and SPLog have the same formats and
column headers. *Trying to count for each doctor, from colA(date) in both
ORLog and SPLog....colE has the doctors abbreviated name in both sheets also.
*My formula is below and have been working on for several hours and am still
getting 0's

=SUMPRODUCT((ORLog!$A$2:$A$65535=C$2)*(ORLog!$E$2: $E$65535=$B2)*(SPLog!$A$2***:$A$65535=C$2)*(SPLog! $E$2:$E$65535=$B2))

hoping someone can help me????- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Or, indeed, perhaps one or more of the dates for that doctor is
outside the range of the dates on the summary sheet - perhaps it was
typed in as March (03), when it should have been May (05).

Pete