+ Reply to Thread
Results 1 to 6 of 6

Calculate total amount partially based on Yearly from other sheet.

  1. #1
    Forum Contributor
    Join Date
    09-13-2013
    Location
    Saudi Arabia
    MS-Off Ver
    2010, 2013
    Posts
    192

    Calculate total amount partially based on Yearly from other sheet.

    Hello Again,

    Procedure Step by Step: -

    Calculating Total Claimed Amount from 13A worksheet.
    1. Look for specific health provider (Segment Code) Column D from PPD worksheet and match with 13A worksheet Column D.
    2. If match then look for PPD start date Column F in PPD worksheet and match with Billing date which is in Column C in 13a worksheet.
    3. Then sum the total claimed amount which is in Column O in 13A Worksheet.

    4. Challenges:
    a. Dates are in different years so.
    i. Calculate the initial date for every year.
    ii. For example:
    1. If the date starts from 01-06-2018 than the sum of amount will be calculating until 2018 December.
    2. If the date starts from 01-02-2019 than the sum of amount will be from 01-02-2019 until December.
    3. If the date starts from 01-03-2020 than the sum of amount will be from 01-03-2020 until December 2020

    Copy of sample is attached for your reference.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Calculate total amount partially based on Yearly from other sheet.

    If I understand correctly then I believe that a formula that will work is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Forum Contributor
    Join Date
    09-13-2013
    Location
    Saudi Arabia
    MS-Off Ver
    2010, 2013
    Posts
    192

    Re: Calculate total amount partially based on Yearly from other sheet.

    Thanks JeteMc for responding to my request.

    Seems the formula is close to my result. the issue is the calculation will be completing year from start date.

    Example: if the date in column F starting from 01-06-2018 and the 13A report having data for multiple years. Then the sum of amount will be like below.

    From 01-06-2018 until 31-12-2018 then
    From 01-01-2019 until 31-12-2019 then
    From 01-01-2020 until last date available in the date.

    But your formula is calculating first year correctly but the further years are not calculating properly because is taking the same month from previous year not the complete month.

    The answer will be follows:-
    • 2018 claimed amount is 641,680.66 (June-Dec 2018)
      2019 claimed amount is 2,690,948.81 (Jan-Dec 2019)
      2020 claimed amount is 239,512,76 (Jan-Feb 2020)


    I hope it is clear now.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Calculate total amount partially based on Yearly from other sheet.

    Please paste the following into cell K3 then copy to cells N3 and Q3:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  5. #5
    Forum Contributor
    Join Date
    09-13-2013
    Location
    Saudi Arabia
    MS-Off Ver
    2010, 2013
    Posts
    192

    Re: Calculate total amount partially based on Yearly from other sheet.

    Perfect.

    I really thankful to you. Your help you don't know how much time you have for me.

    I will close the topic and add the reputation to your account.

    Thanks once again.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Calculate total amount partially based on Yearly from other sheet.

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Macro updating of invoice sheet monthly yearly and total
    By omarsam in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-14-2016, 04:02 AM
  2. [SOLVED] Forcast a Yearly Total Based upon Year-to-date Performance
    By selfevident1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-29-2016, 08:53 PM
  3. Replies: 3
    Last Post: 05-09-2012, 01:43 PM
  4. Calculate amount based on circumference / divide total in 2 locations
    By JACKIECOTHRON in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-25-2012, 02:55 PM
  5. How can I calculate amount of time left based on amount spent?
    By KLD in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-23-2006, 11:25 AM
  6. [SOLVED] How do I calculate Amount of Sales Tax from Total Amount?
    By MikeS in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-26-2005, 04:06 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1