Results 1 to 7 of 7

Sum YTD and Quarters using Index and Match and Sumifs between months across columns

Threaded View

  1. #1
    Registered User
    Join Date
    11-29-2019
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    3

    Sum YTD and Quarters using Index and Match and Sumifs between months across columns

    Hi, I'm working on a new spreadsheet to show YTD and Quarterly activity.
    My data set is organised with the months being horizontally across the columns and the activities vertically down the rows.
    Spreadsheet uploaded with this post (with dummy data).
    My data set will have new rows and columns added each month.
    I have tried Sumproduct but the range needs to be specified and is not dynamic allowing new rows or columns each month.
    The attached spreadsheet is using Index and Match and Sumifs.
    I would like to continue to use these if possible to calculate YTD and Quarters QTD (see cells highlighted in red) with criteria in droplists for the start month and end month (see cells A1 and A2).
    There is multiple criteria so the individual activities can be itemized per row.

    Please help... my brain is about to explode!
    I've spent hours looking through the internet and Youtube and just can't figure this one out by myself.

    My current formula for MTD is as follows:
    =SUMIFS(INDEX('YTD stats 2019'!$J:$ZZ,0,MATCH($A$2,'YTD stats 2019'!$J$2:$ZZ$2,0)),'YTD stats 2019'!$B:$B,A7,'YTD stats 2019'!$C:$C,B7,'YTD stats 2019'!$D:$D,C7,'YTD stats 2019'!$I:$I,$A$3)
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 04-17-2018, 03:04 PM
  2. [SOLVED] SUMIFS INDEX MATCH on different columns?
    By s7upid in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-10-2017, 02:27 AM
  3. [SOLVED] Sumifs or lookup or index/match based on columns and rows
    By ned0 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-10-2013, 08:48 PM
  4. [SOLVED] IF AND or SUMIFS comparing Months from 2 date columns and text from 2 other columns
    By jrochet in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 09-22-2012, 11:06 AM
  5. Excel 2007 : Quarters and months
    By APPLEBEE in forum Excel General
    Replies: 6
    Last Post: 06-01-2011, 02:02 PM
  6. Averages using Quarters & Months
    By jimbb in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-13-2010, 07:13 PM
  7. Translating Months into Quarters
    By jchao in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-03-2008, 02:46 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