+ Reply to Thread
Results 1 to 4 of 4

Formula required to make Aging Sheet

  1. #1
    Registered User
    Join Date
    11-07-2017
    Location
    India
    MS-Off Ver
    2013
    Posts
    63

    Formula required to make Aging Sheet

    Hello All,

    I am using Excel 2013 and need a formula to calculate ageing of various equipment installed in different plants

    I have made a sample for my problem which is attached herewith

    I need a formula in C10, E10 and G10 which I can drag down to fill up the values

    C10 - should count the Equipment from A2 till down and compare each equipment with the year in C2 till down and match the year with Period in B10 till down

    E10 - should increment by the count of Eq in respective years

    G10 - will sum the values for respective year comparing it with the Period

    Sample data is small actual data will be much more

    I have given some explanation next to each cell where the formula is required

    Cells colored for explanation purpose only.

    Hope to get some help
    TIA
    Sameer
    Attached Files Attached Files
    Last edited by sameer79; 10-11-2019 at 01:42 AM.

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,006

    Re: Formula required to make Aging Sheet

    Please try at

    C10
    =COUNTIF($C$2:$C$6,"<"&DATE(B10+1,1,1))

    Not sure why your sample increment by 2 on E10 to E11
    E11
    =E10+C11

    G10
    =SUMIFS(G$2:G$6,$C$2:$C$6,"<"&DATE($B10+1,1,1))

  3. #3
    Registered User
    Join Date
    11-07-2017
    Location
    India
    MS-Off Ver
    2013
    Posts
    63

    Re: Formula required to make Aging Sheet

    Dear Ray
    I will test with my actual data and revert
    Thanks for your time

  4. #4
    Registered User
    Join Date
    11-07-2017
    Location
    India
    MS-Off Ver
    2013
    Posts
    63
    Quote Originally Posted by sameer79 View Post
    Dear Ray
    I will test with my actual data and revert
    Thanks for your time
    Dear Ray

    I tested your formula.

    It works perfectly.
    Thanks for your help and time

+ 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. Required vba for make index sheet in workbook
    By santosh226001 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-26-2017, 07:30 AM
  2. Clicking on Data sheet and have it make different sheet required Summary
    By kuldeep1 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-13-2016, 12:30 PM
  3. How to make this formula in excell sheet..please help me to make this..
    By mdinesh223 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-13-2015, 05:01 AM
  4. [SOLVED] Formula required to match a value in sheet 1 to sheet 2 Column A and select a value from B
    By deputydoug in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-10-2015, 08:44 AM
  5. [SOLVED] formula required to filter sheet
    By cfinch100 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 09-27-2014, 01:11 PM
  6. [SOLVED] Formula required- A3= 1S; how to make A4=2S, A5=3S
    By thadacto in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-27-2013, 06:30 PM
  7. [SOLVED] How to make aging possible in Excel for an age trial balance shee.
    By Anne L in forum Excel General
    Replies: 3
    Last Post: 03-20-2005, 02: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