# Trying to do a sumifs with mutiple text critera that can change

1. ## Trying to do a sumifs with mutiple text critera that can change

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.  Register To Reply

2. ## Re: Trying to do a sumifs with mutiple text critera that can change

Please attach a sample file showing realistic & representative sample data WITHOUT confidential information. See the yellow band at the top of the page for more info on how to attach a file.  Register To Reply

3. ## Re: Trying to do a sumifs with mutiple text critera that can change

Ok I have done a small sample of the file with the numbers anonymised. Please note that there are normally a lot more sheets and considerable more data. One of the problems is the size of the file.

The cell in A6 on the HL tab is downloaded from an outside source and that is what drives the month I have to use. I chose that cell by setting it in the outside source. The data while not all data is in exactly the same format as is downloaded and that format cannot be changed.  Register To Reply

4. ## Re: Trying to do a sumifs with mutiple text critera that can change

My idea for a variable amount of months would be to make a table with them and refer to its column name.

Like for example if you make a Table in the 'Lookup Period' sheet and name the column with month numbers 00, 01, 02 etc., for example Mt and then in your formulas then refer to it as Table1[Mt].
Then whatever number of months you add they will be included in your formulas as long as they are included in Table1[Mt] column.

So in the formula in post #1 you would put =SUM(SUMIFS('DATA-ACT-YTD'!\$G:\$G,'DATA-ACT-YTD'!\$A:\$A,\$A9&"-"&\$A\$4,'DATA-ACT-YTD'!\$M:\$M,Table1[Mt]))*\$B9
And you would enter it as an array formula with CTRL+SHIFT+ENTER.  Register To Reply

5. ## Re: Trying to do a sumifs with mutiple text critera that can change

OK tried that either I am doing something wrong or that doesn't work (i'm probably doing something wrong). In effect the months to be used are currently calculated so I have moved that set of calculations to the Lookup period tab in J1 to J14 and then added the formula you suggested in cell G3 on the HK tab but even entering it as a array formula it just gives me an error. I've attached the revised spreadsheet.  Register To Reply

6. ## Re: Trying to do a sumifs with mutiple text critera that can change

Uh, where is the revised sheet?  Register To Reply

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