I have a spreadsheet where a large and variable amount of data gets downloaded in one sheet, in another sheet I have the formula to get the sums of the data based on the criteria. The values of the data are all in column G and there are 2 criteria that I am using to calculate the sumifs. The first is in column a of the data sheet and the second is in column M of the data sheet. Column M is actually the month number but expressed in text format so month 1 is expressed as "01". This data comes from an outside source and cannot be downloaded as a number. The formula I am using is =SUMIFS('DATA-ACT-YTD'!$G:$G,'DATA-ACT-YTD'!$A:$A,$A9&"-"&$A$4,'DATA-ACT-YTD'!$M:$M,$A$448) where DATA-ACT-YTD is the data sheet. A9 is the criteria representing a fixed value for that sheet. What I want to do is have a formula that covers multiple months based on what month we are in to get a year to date value. A448 to A462 contain all the possible months. I have managed a formula for these so they will only contain the months I want. I can get the correct result by adding multiple sumifs but this makes the spreadsheet large and unwieldy
This formula =SUM(SUMIFS('DATA-ACT-YTD'!$G:$G,'DATA-ACT-YTD'!$A:$A,$A9&"-"&$A$4,'DATA-ACT-YTD'!$M:$M,{"02","01"}))*$B9 will get me the correct values for months one and two. However the amount of months that I want to add up is variable nd I can't find a way of having a vriable set of text fields within the braces, any help appreciated.
File is huge and calculates loads of financial data.
All help gratefully received
Bookmarks