+ Reply to Thread
Results 1 to 7 of 7

Formula to Sum Charges Based on Dates for each Patient

  1. 10-26-2011, 04:19 PM #1
    chuckwud
    chuckwud is offline
    Registered User
    Join Date
    10-26-2011
    Location
    Georgia
    MS-Off Ver
    Excel 2003
    Posts
    4

    Formula to Sum Charges Based on Dates for each Patient

    Hello, i need your help. Please see Attached Excel worksheet.

    I need a formula here that will look at the "charge worksheet" and if column "A" equals column A on the "Patients worksheet" and if the date of the charge corrosponding to the matching Patient falls between the "Admit" and "Discharge" dates, then sum those charges and return the summed value.

    Thanks in advance.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 10-26-2011, 05:44 PM #2
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,050

    Re: Formula to Sum Charges Based on Dates for each Patient

    Try:

    =SUMPRODUCT(--(Charges!$A$3:$A$10=Patients!$A3),--(Charges!$B$3:$B$10>=$B3),--(Charges!$B$3:$B$10<=$C3),(Charges!$C$3:$C$10))

    Adjust the ranges as required. They must all be the same size (same number of cells/row)

    Regards
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 10-26-2011, 06:28 PM #3
    chuckwud
    chuckwud is offline
    Registered User
    Join Date
    10-26-2011
    Location
    Georgia
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Formula to Sum Charges Based on Dates for each Patient

    Thanks, i'll try that
    Reply With QuoteRegister To Reply
  4. 10-27-2011, 10:04 AM #4
    chuckwud
    chuckwud is offline
    Registered User
    Join Date
    10-26-2011
    Location
    Georgia
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Formula to Sum Charges Based on Dates for each Patient

    Hello, the formula doesn't seem to be working...Can you assist again?

    Thanks
    Reply With QuoteRegister To Reply
  5. 10-27-2011, 10:13 AM #5
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,050

    Re: Formula to Sum Charges Based on Dates for each Patient

    the formula doesn't seem to be working
    In what way?


    Regards
    Reply With QuoteRegister To Reply
  6. 10-27-2011, 10:29 AM #6
    chuckwud
    chuckwud is offline
    Registered User
    Join Date
    10-26-2011
    Location
    Georgia
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Formula to Sum Charges Based on Dates for each Patient

    Heres the attachment of what i did. maybe im doing something wrong. The charges are showing all "0"

    Thanks
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 10-27-2011, 11:03 AM #7
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,050

    Re: Formula to Sum Charges Based on Dates for each Patient

    Have a look at your discharge dates.


    Regards
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1