+ Reply to Thread
Results 1 to 7 of 7

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

  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

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    27,247

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

    We don't want your brain to explode, do we?

    I don't want mine to explode, either so, I cut the data rows back to about 20 to facilitate manual checking. Please - we do not need hundreds of rows.

    I'd go for SUMPRODUCT here... Do not use whole column references, as you may get performance issues - unless you do have 1,000,000 rows. Use something REASONABLE, but future-proof.

    Month to date:
    =SUMPRODUCT(('YTD stats 2019'!$B$3:$B$20=Summary!$A7)*('YTD stats 2019'!$J$2:$AW$2=Summary!$A$2)*('YTD stats 2019'!$I$3:$I$20=Summary!$A$3)*'YTD stats 2019'!$J$3:$AW$20)

    Year to date:
    =SUMPRODUCT(('YTD stats 2019'!$B$3:$B$20=Summary!$A7)*('YTD stats 2019'!$J$1:$AW$1=YEAR(Summary!$A$2))*('YTD stats 2019'!$I$3:$I$20=Summary!$A$3)*'YTD stats 2019'!$J$3:$AW$20)

    Quarters (drag across):
    =SUMPRODUCT(('YTD stats 2019'!$B$3:$B$20=Summary!$A7)*('YTD stats 2019'!$J$1:$AW$1=YEAR(Summary!$A$2))*(1+INT((MONTH('YTD stats 2019'!$J$2:$AW$2)-1)/3)=COLUMNS($R:R))*('YTD stats 2019'!$I$3:$I$20=Summary!$A$3)*'YTD stats 2019'!$J$3:$AW$20)
    Attached Files Attached Files
    Glenn



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

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

    Thanks for your super-fast reply Glenn!
    The way you calculated QTD looks really interesting. I'll try and use that method in future.
    I really want to avoid Sumproduct because the data set will increase each month (both rows and columns).
    I also want to avoid it due to the length of waiting time required while the processors calculate the large amount of data.
    Using the Sumproduct formula you've provided results in an error error message when more than 55000 is used (Excel ran out of resources while attempting to calculate...). My data is more than 55000 rows. Unfortunately it's just not practicable.
    Do you think there is a method using Sumifs with Index and Match?

    That being said, I really appreciate your help and I'm a big fan of the Emerald Isle.

  4. #4
    Valued Forum Contributor
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    976

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

    A few things:
    My first recommendation would be to make use of Excel Tables on sheet "YTD stats 2019". That way you can refer to the table columns so the sumproduct formulas will grow automatically as the table grows. I tested the spreadsheet I've attached with over 56,000 rows and there wasn't any problem with the formulas.

    Also, as a different option, I used this for the MTD calculation:

    =SUMIFS(OFFSET(Table1[7/1/2016],0,DATEDIF("7/1/2016",$A$2,"M")),Table1[ESTABLISHMENT],$A7,Table1[DIVISION],$B7,Table1[ESTABLISHMENT_SPECIALTY],Summary!$C7,Table1[Weighting],$A$3)

    This will work as you continue to expand your columns and rows (as long as you keep your data back through July of 2016).

    Also, I changed the YTD formula slightly so that as you change the date in A2 on the Summary Sheet, the YTD goes through that date and doesn't add up all the columns with the same year.

    =SUMPRODUCT((Table1[ESTABLISHMENT]=$A7)*(YEAR(Table1[[#Headers],[7/1/2016]:[11/1/2019]])=YEAR($A$2))*(MONTH(Table1[[#Headers],[7/1/2016]:[11/1/2019]])<=MONTH($A$2))*(Table1[Weighting]=$A$3)*(Table1[[7/1/2016]:[11/1/2019]]))

    Also, to make these work, I changed the headers for the month columns to actual dates. On the "YTD stats 2019" sheet I also changed Row 1 with the year to be a formula since I change the headers to an actual date, but because of changing the column headers to actual dates I don't really need the years shown in row one anyway.

    I changed the QTD formula to work with the Table.

    I think I could have done more changes to make it more dynamic but it's getting very late here now - I'm going to sleep.
    Attached Files Attached Files

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    27,247

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

    Really??

    Here are 61,000 rows, with ranges set to 100,000 in the SPs and it takes about 2 seconds to calculate!!

    I suspect OFFSET will be a pain: being volatile, it re-calculates if anything changes....
    Attached Files Attached Files
    Last edited by Glenn Kennedy; 12-01-2019 at 05:01 AM.

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

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

    Thanks again for your help lads! SOLVED

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    27,247

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

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

+ 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: 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