+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    10-24-2018
    Location
    London
    MS-Off Ver
    2016
    Posts
    3

    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.

    All help gratefully received

  2. #2
    Forum Contributor
    Join Date
    10-30-2019
    Location
    Bistrita, Romania
    MS-Off Ver
    2013; 365
    Posts
    221

    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.

    Having an example would help us help you better and faster.
    To show your appreciation
    Click ★ Add reputation!

  3. #3
    Registered User
    Join Date
    10-24-2018
    Location
    London
    MS-Off Ver
    2016
    Posts
    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.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    10-30-2019
    Location
    Bistrita, Romania
    MS-Off Ver
    2013; 365
    Posts
    221

    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.

  5. #5
    Registered User
    Join Date
    10-24-2018
    Location
    London
    MS-Off Ver
    2016
    Posts
    3

    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.

  6. #6
    Forum Contributor
    Join Date
    10-30-2019
    Location
    Bistrita, Romania
    MS-Off Ver
    2013; 365
    Posts
    221

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

    Uh, where is the revised sheet?

+ 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. SUMIFS - Multiple Criteria within a single critera
    By nymetsfan25 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 07-18-2019, 04:20 PM
  2. Sumifs with mutiple choice same column
    By pccamara in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-18-2018, 08:17 AM
  3. [SOLVED] SUMIFS (Several Critera)
    By mackan7695 in forum Excel General
    Replies: 5
    Last Post: 09-15-2015, 03:24 AM
  4. [SOLVED] SUMIFS, COUNTIFS, Using a date as one of the critera
    By fgruhlke in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-07-2014, 10:12 PM
  5. [SOLVED] SUMIFS with mutiple worksheets
    By figo12 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-07-2013, 07:22 PM
  6. [SOLVED] SUMIFS with <> Critera
    By bdicarlo1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-20-2013, 01:35 PM
  7. [SOLVED] Returning sum based on row and column critera (SUMIFS??)
    By PERE in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-18-2013, 11:55 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