+ Reply to Thread
Results 1 to 4 of 4

SUMIF last n columns YTD

  1. #1
    Registered User
    Join Date
    06-12-2014
    Posts
    19

    SUMIF last n columns YTD

    Hello,

    I'm building a tracking document with monthly data in which I need to calculate YTD this year and YTD last year (for the same period). Currently I am calculating the YTD TY and LY with the formulas below.

    Please Login or Register  to view this content.
    I'm hoping to be able to replace the range to be dynamic. Ideally I would like the TY YTD range to be the 12 columns directly to the left, and I would like the range for the LY YTD to be the 12 columns, to the left of this (OFFSET -12).

    I appreciate any assistance, thanks!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor Root_'s Avatar
    Join Date
    07-29-2017
    Location
    _
    MS-Off Ver
    2010+
    Posts
    416

    Re: SUMIF last n columns YTD

    See if the following formulas work for you:

    for TY YTD, =SUMIFS(3:3,$2:$2,">="&DATE(YEAR(TODAY()),1,1),$2:$2,"<="&TODAY())
    for LY YTD, =SUMIFS(3:3,$2:$2,">="&DATE(YEAR(TODAY())-1,1,1),$2:$2,"<="&TODAY()-400)

  3. #3
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    638

    Re: SUMIF last n columns YTD

    Z3=SUMPRODUCT((MONTH($B$2:$Y$2)<=MONTH($D$2))*(YEAR($B$2:$Y$2)=LEFT(Z$2,4)+0)*($B3:$Y3)) copy across and down

  4. #4
    Registered User
    Join Date
    06-12-2014
    Posts
    19

    Re: SUMIF last n columns YTD

    Thanks, all!

+ 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. Sumif across columns
    By jw01 in forum Excel General
    Replies: 8
    Last Post: 06-29-2017, 02:26 PM
  2. SUMIF 2 columns?
    By kevinSHR in forum Excel General
    Replies: 3
    Last Post: 05-04-2016, 07:56 PM
  3. sumif with not columns far apart
    By makinmomb in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-05-2014, 04:24 PM
  4. [SOLVED] Sumif two columns matching another two columns
    By kl99ny in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-11-2013, 02:08 PM
  5. SUMIF Several Columns
    By lampoonsaat in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-27-2011, 01:11 PM
  6. SumIf across columns
    By Lithium78 in forum Excel General
    Replies: 12
    Last Post: 02-19-2010, 04:01 AM
  7. Sumif across columns
    By mminsf in forum Excel Formulas & Functions
    Replies: 52
    Last Post: 09-06-2005, 12:05 PM

Tags for this Thread

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