Sumproduct on multiple sheets...please help???

[Tasha]

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????

 

[Pete_UK]

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

 

[Tasha]

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]

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

 

[Tasha]

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]

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

Pete

 

[Tasha]

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]

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

 

[Pete_UK]

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

 

[Tasha]

I checked all the dates, and are June dates, formatted correctly. Then I
filtered for just his name and checked for spacing....is ok. I did notice
that his name was the first one on the list for June 2nd, maybe ironic? but
wondering if it's got something to do with only his not showing up?

 

[Tasha]

Pete, I figured it out.....the sheet that I am linked to for the ORLog was
missing a physician on one of the patients. I called and had them enter the
surgeon in, checked my sheet and it's correct now.... thanks for your
help!!!!

 

[Pete_UK]

Glad that you got to the bottom of it, Tasha - thanks for keeping me
informed (and verifying that the formula works !!).

Pete