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

1. ## 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.
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.

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)

2. ## 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)

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

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. ## 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.

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.

5. ## 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....

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

You're welcome.

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.

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

#### 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