+ Reply to Thread
Results 1 to 2 of 2

QTD Calculation with SUMIFS but not using dates

  1. #1
    Forum Contributor
    Join Date
    01-28-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2013
    Posts
    200

    QTD Calculation with SUMIFS but not using dates

    Hello,

    Currently I want to sum sales based for MTD, QTD, and YTD. I don't use dates, but instead use a combination of the year and the month (i.e. 201601 or 201612). I use sumifs to grab items between two dates. MTD is easy:

    =SUMIFS(TableA[SALES],TableB[CATEGORY],$A23,TableB[fiscalMoAsNo],A22) where A22 is some calculated date like 201612

    YTD not too bad:

    =SUMIFS(TableA[SALES],TableB[CATEGORY],$A23,TableB[fiscalMoAsNo],">="&A21,TableB[fiscalMoAsNo],"<="&A22)

    A21 always is the 1st month of the year like 201601 and A22 is the current month like 201612 so it gets automatically selected.

    Stuck on how to do this for QTD. Essentially I am thinking to use the formula for YTD but I need both of the fiscalMoAsNo to change - One to determine the first month of the Quarter and the second to use the current month. Current month just is the A22 value, but how to choose the A21 or first month of the Qtr?

    Can someone suggest some possible formulas or perhaps a psuedocode that would follow this logic? Thanks!

    Vas

  2. #2
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: QTD Calculation with SUMIFS but not using dates

    assuming the current month number in cell A1, the following formula will give you the first month of the current Quarter:
    Please Login or Register  to view this content.
    Pierre Leclerc
    _______________________________________________________

    If you like the help you got,
    Click on the STAR "Add reputation" icon at the bottom.

+ 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. Replies: 14
    Last Post: 10-16-2015, 04:59 PM
  2. [SOLVED] How to speed up the SUMIFS calculation
    By Villalobos in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-21-2015, 02:01 PM
  3. Sumifs Formula using vlookup to chose which YTD calculation to use
    By mvparker79 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-18-2015, 07:37 PM
  4. Calculation of dates, return blank when one of the dates is not populated
    By brian_2me in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-15-2014, 10:59 AM
  5. Replies: 7
    Last Post: 07-01-2014, 08:15 AM
  6. [SOLVED] excel formula to help my calculation from min dates of 3 dates cell ranges
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-04-2013, 10:32 AM
  7. Commission Calculation using Sumifs
    By tsummers2 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-07-2011, 04:08 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