Problem with SUMPRODUCT....help please!
I am having trouble getting a count from a worksheet with multiple criteria.
Can someone look at this and see what I am doing wrong? I have two worksheets, [DAILY ADMITS WORKSHEET.xls].[dly wrksht] is the daily worksheet that changes every day, [PHYS_STATS_MTD] is the one I am putting the calculation into for each day of the month. In [PHYS_STATS_MTD], column B lists the PHYNO down(starting in cell B3) and column D lists the date of the month across starting with cell D2, however is only the number of the month. Formated to only show 'd'. Date is also set up that way in the Daily Admits worksheet. I am trying to get a count of all physicians that had admits for that day. It keeps giving me a 0. =SUMPRODUCT(('[DAILY ADMITS WORKSHEET.xls]dly wrksht'!$D$136=B3)*('[DAILY ADMITS WORKSHEET.xls]dly wrksht'!$B$2:$B$136=D2)) |
Problem with SUMPRODUCT....help please!
The ranges have to be the same size
=SUMPRODUCT(('[DAILY ADMITS WORKSHEET.xls]dly wrksht'!$D$2:$D$136=B3)* ('[DAILY ADMITS WORKSHEET.xls]dly wrksht'!$B$2:$B$136=D2)) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tasha" wrote in message ... I am having trouble getting a count from a worksheet with multiple criteria. Can someone look at this and see what I am doing wrong? I have two worksheets, [DAILY ADMITS WORKSHEET.xls].[dly wrksht] is the daily worksheet that changes every day, [PHYS_STATS_MTD] is the one I am putting the calculation into for each day of the month. In [PHYS_STATS_MTD], column B lists the PHYNO down(starting in cell B3) and column D lists the date of the month across starting with cell D2, however is only the number of the month. Formated to only show 'd'. Date is also set up that way in the Daily Admits worksheet. I am trying to get a count of all physicians that had admits for that day. It keeps giving me a 0. =SUMPRODUCT(('[DAILY ADMITS WORKSHEET.xls]dly wrksht'!$D$136=B3)*('[DAILY ADMITS WORKSHEET.xls]dly wrksht'!$B$2:$B$136=D2)) |
Problem with SUMPRODUCT....help please!
I'm confused about the date and date format.
What exactly is in B3:B136 and what is in D2? For a particular day, if B3:B3136 hold dates as a DATE format, then D2 needs to be the full date i.e. to match day (16th July) D2 should be 16/07/2007 (UK dd/mm/y format). How a date is formatted does represent how it is stored internally. "Tasha" wrote: I am having trouble getting a count from a worksheet with multiple criteria. Can someone look at this and see what I am doing wrong? I have two worksheets, [DAILY ADMITS WORKSHEET.xls].[dly wrksht] is the daily worksheet that changes every day, [PHYS_STATS_MTD] is the one I am putting the calculation into for each day of the month. In [PHYS_STATS_MTD], column B lists the PHYNO down(starting in cell B3) and column D lists the date of the month across starting with cell D2, however is only the number of the month. Formated to only show 'd'. Date is also set up that way in the Daily Admits worksheet. I am trying to get a count of all physicians that had admits for that day. It keeps giving me a 0. =SUMPRODUCT(('[DAILY ADMITS WORKSHEET.xls]dly wrksht'!$D$136=B3)*('[DAILY ADMITS WORKSHEET.xls]dly wrksht'!$B$2:$B$136=D2)) |
Problem with SUMPRODUCT....help please!
that worked, I hadn't noticed my range begin was missing.....thanks!!!!
"Bob Phillips" wrote: The ranges have to be the same size =SUMPRODUCT(('[DAILY ADMITS WORKSHEET.xls]dly wrksht'!$D$2:$D$136=B3)* ('[DAILY ADMITS WORKSHEET.xls]dly wrksht'!$B$2:$B$136=D2)) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tasha" wrote in message ... I am having trouble getting a count from a worksheet with multiple criteria. Can someone look at this and see what I am doing wrong? I have two worksheets, [DAILY ADMITS WORKSHEET.xls].[dly wrksht] is the daily worksheet that changes every day, [PHYS_STATS_MTD] is the one I am putting the calculation into for each day of the month. In [PHYS_STATS_MTD], column B lists the PHYNO down(starting in cell B3) and column D lists the date of the month across starting with cell D2, however is only the number of the month. Formated to only show 'd'. Date is also set up that way in the Daily Admits worksheet. I am trying to get a count of all physicians that had admits for that day. It keeps giving me a 0. =SUMPRODUCT(('[DAILY ADMITS WORKSHEET.xls]dly wrksht'!$D$136=B3)*('[DAILY ADMITS WORKSHEET.xls]dly wrksht'!$B$2:$B$136=D2)) |
Problem with SUMPRODUCT....help please!
thanks Bob, that fixed the problem, I hadn't noticed my range begin cell was
missing!!!! "Bob Phillips" wrote: The ranges have to be the same size =SUMPRODUCT(('[DAILY ADMITS WORKSHEET.xls]dly wrksht'!$D$2:$D$136=B3)* ('[DAILY ADMITS WORKSHEET.xls]dly wrksht'!$B$2:$B$136=D2)) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tasha" wrote in message ... I am having trouble getting a count from a worksheet with multiple criteria. Can someone look at this and see what I am doing wrong? I have two worksheets, [DAILY ADMITS WORKSHEET.xls].[dly wrksht] is the daily worksheet that changes every day, [PHYS_STATS_MTD] is the one I am putting the calculation into for each day of the month. In [PHYS_STATS_MTD], column B lists the PHYNO down(starting in cell B3) and column D lists the date of the month across starting with cell D2, however is only the number of the month. Formated to only show 'd'. Date is also set up that way in the Daily Admits worksheet. I am trying to get a count of all physicians that had admits for that day. It keeps giving me a 0. =SUMPRODUCT(('[DAILY ADMITS WORKSHEET.xls]dly wrksht'!$D$136=B3)*('[DAILY ADMITS WORKSHEET.xls]dly wrksht'!$B$2:$B$136=D2)) |
Problem with SUMPRODUCT....help please!
thanks for your reply, I was missing my range begin cell in my formula as Bob
so kindly pointed out for me!! :) Have a great day! "Toppers" wrote: I'm confused about the date and date format. What exactly is in B3:B136 and what is in D2? For a particular day, if B3:B3136 hold dates as a DATE format, then D2 needs to be the full date i.e. to match day (16th July) D2 should be 16/07/2007 (UK dd/mm/y format). How a date is formatted does represent how it is stored internally. "Tasha" wrote: I am having trouble getting a count from a worksheet with multiple criteria. Can someone look at this and see what I am doing wrong? I have two worksheets, [DAILY ADMITS WORKSHEET.xls].[dly wrksht] is the daily worksheet that changes every day, [PHYS_STATS_MTD] is the one I am putting the calculation into for each day of the month. In [PHYS_STATS_MTD], column B lists the PHYNO down(starting in cell B3) and column D lists the date of the month across starting with cell D2, however is only the number of the month. Formated to only show 'd'. Date is also set up that way in the Daily Admits worksheet. I am trying to get a count of all physicians that had admits for that day. It keeps giving me a 0. =SUMPRODUCT(('[DAILY ADMITS WORKSHEET.xls]dly wrksht'!$D$136=B3)*('[DAILY ADMITS WORKSHEET.xls]dly wrksht'!$B$2:$B$136=D2)) |
All times are GMT +1. The time now is 04:54 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com