Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct on multiple sheets...please help???
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???? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct on multiple sheets...please help???
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???? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct on multiple sheets...please help???
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???? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct on multiple sheets...please help???
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 - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct on multiple sheets...please help???
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 - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct on multiple sheets...please help???
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 !!!! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct on multiple sheets...please help???
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 - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct on multiple sheets...please help???
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 - |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct on multiple sheets...please help???
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct on multiple sheets...please help???
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? "Pete_UK" wrote: 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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct on multiple sheets...please help???
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" wrote: 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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct on multiple sheets...please help???
Glad that you got to the bottom of it, Tasha - thanks for keeping me
informed (and verifying that the formula works !!). Pete On Jun 26, 2:11*pm, Tasha wrote: 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!!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct for multiple sheets | Excel Worksheet Functions | |||
Multiple Sheets (Need to create 500 individual sheets in one workbook, pulling DATA | Excel Worksheet Functions | |||
sumproduct looking at multiple sheets | Excel Worksheet Functions | |||
Sumproduct accross multiple sheets | Excel Discussion (Misc queries) | |||
sumproduct from multiple sheets | Excel Worksheet Functions |