YTD Sum
Results 1 to 3 of 3

Thread: YTD Sum
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Sep 2016
    Posts
    101
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default YTD Sum

    Firstly, apologies for no dropbox/image - both methods are banned on my company server.

    Criteria
    - Columns B4:B11 with Project 1, Project 2, Project 3...Project 8
    - (Actuals) Columns C3:N3 (Apr 20 through to Mar 21)
    - (Budget) Columns O3:Z3 (Apr 20 through to Mar 21)
    - Current month (Jul 20) = B3

    FORMULAS
    Actual YTD Formula: Project 7
    Look down B4:B11 for Project 7
    Look across C3:N3 for everything that is =< Jul 20
    Sum everything for Project 7 in those months

    Budget YTD Formula: Project 3
    Look down B4:B11 for Project 7
    Look across O3:Z3 for everything that is =< Jul 20
    Sum everything for Project 3

    FYI - the numbers in the month/project fields do not matter, they can be completely arbitrary.

    If anyone can help, it would be much appreciated.

    Thanks
    Ryan
    Last edited by ryansm05; Aug 9th, 2019 at 08:08 AM.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,976
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: YTD Sum

    Are the value in B3, C3:N3 & O3:Z3 text values like Jul 20 or are they dates like 01/07/2020 formatted to show Jul 20
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,239
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: YTD Sum

    Maybe

    Project 7 Actual YTD
    =SUMIFS(INDEX($C$4:$O$11,MATCH(B14,B$4:B$11,0),0),C$3:O$3,"<="&B$3)
    where B14 = Project 7

    Project 3 Budget YTD
    =SUMIFS(INDEX($P$4:$Z$11,MATCH(B15,B$4:B$11,0),0),P$3:Z$3,"<="&B$3)
    where B15 = Project 3

    Hope this helps

    M.
    Last edited by Marcelo Branco; Aug 9th, 2019 at 08:30 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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