Lightest formula for sumifs with multiple criteria

[cgsierra]

Hello,
I have a database set up in "Dbase" tab as follows:
Column A labeled LE, Column B labelled GTM, C labeled LOC, D labeled DEP, E labeled Amount. Each of these columns have over 20 different possible values. IE. LE (over 20 different possible values found in the database, ie 3,7,9,12,24,72,65,...), GTM (over 20 different possible values, ie 000,003,007,006,010,019,001,...), LOC (over 20 different possible values, ie 0101,0102,1245,2452,1110,0099,4215,...), DEP (over 20 different possible values, ie 1254,7845,0011,1289,6411,0001,4582,...)

I then have a second tab called "Output" set up as follows:
B3 = LE
B4 = GTM
B5 = LOC
B6 = DEP

range C3:F3 is used to type the multiple criteria used to look in Column A (LE) in the Dbase tab (4 or less possible criteria allowed, 1 in each column, ie C3=7, D3=12)

range C4:F4 is used to type the multiple criteria used to look in Column B (GTM) in the Dbase tab (4 or less possible criteria allowed, 1 in each column, ie C4=000, D4=019, E4=010, F4=003)

range C5:F5 is used to type the multiple criteria used to look in Column C (LOC) in the Dbase tab (4 or less possible criteria allowed, 1 in each column, ie C5=0101, D5=0102, E5=0099)

range C6:F6 is used to type the multiple criteria used to look in Column D (DEP) in the Dbase tab (4 or less possible criteria allowed, 1 in each column, ie C6=7845, D6=6411, E6=4582, F6=1289)

Once I enter the multiple criteria in the ranges mentioned above, the formula in cell b12 in tab "Output" should add column E (amount) for all the data in the Raw Data tab that fits the multiple criteria stated above.

I am looking for the lightest possible formula to execute this as the formula in b12 will be dragged over for about 500 rows and across 100 columns for a single sheet. The file itself may have about 10 of those Output sheets.

Please help!

 

[cgsierra]

Were the pics helpful? Please let me know if you need additional info. Thank you!

 

[cgsierra]

Were the pics helpful? Please let me know if you need additional info. Thank you!

Hello again,
the formula in the picture is the following:
=SUMPRODUCT(ISNUMBER(MATCH('Raw Data'!$A$1:$A$500000,J2:M2,0))*ISNUMBER(MATCH('Raw Data'!$B$1:$B$500000,J3:M3,0))*ISNUMBER(MATCH('Raw Data'!$C$1:$C$500000,J4:M4,0))*ISNUMBER(MATCH('Raw Data'!$D$1:$D$500000,J6:M6,0))*ISNUMBER(MATCH('Raw Data'!$E$1:$E$500000,J7:M7,0))*ISNUMBER(MATCH('Raw Data'!$F$1:$F$500000,J8:M8,0))*ISNUMBER(MATCH('Raw Data'!$G$1:$G$500000,$G35,0))*ISNUMBER(MATCH('Raw Data'!$L$1:$L$500000,K$10,0))*ISNUMBER(MATCH('Raw Data'!$I$1:$I$500000,MONTH(K$11),0))*ISNUMBER(MATCH('Raw Data'!$J$1:$J$500000,YEAR(K$11),0)),'Raw Data'!$K$1:$K$500000)


I used to have the following formula which worked when I only had criteria in cells J2:J8. It allowed for wildcards too. However, as you can see from the picture, I now need more flexibility (criteria in highlighted range of J2:M7). Therefore, this formula no longer works:
=SUMIFS('Raw Data'!$K$1:$K$493665,'Raw Data'!$L$1:$L$493665,K$10,'Raw Data'!$J$1:$J$493665,YEAR(K$11),'Raw Data'!$I$1:$I$493665,MONTH(K$11),'Raw Data'!$A$1:$A$493665,$J$2,'Raw Data'!$B$1:$B$493665,$J$3,'Raw Data'!$C$1:$C$493665,$J$4,'Raw Data'!$D$1:$D$493665,$J$6,'Raw Data'!$E$1:$E$493665,$J$7,'Raw Data'!$F$1:$F$493665,$J$8,'Raw Data'!$G$1:$G$493665,$G35)

hope this helps

 

[iggy_]

Given all the parameters I can't think of a way to cut it down , hopefully someone else can help.. bump

 

[cgsierra]

Are the values in LE, GTM, LOC, DEP numeric or alphanumeric? I see some of your examples have preceding zeros, which would imply alphanumeric. How many rows are in your database? Are there any empty cells? Unfortunately SUMIFS only allows 2 arrays, and you have 4. This will necessitate an array formula, which will not be "light". Would you consider a macro? Depending on how big your database is, we could set it up to run whenever any of the values in C3:F6 are changed.

And I don't understand where you want to drag this formula to. If they are refer to the same C3:F6, and the same columns on your DBase tab, they'll all return the same value.

Hello Eric, given the pictures that I provided and the sumifs formula that I am using (see below), how can I expand/rewrite it to include 2 arrays? There may be a chance that I only need 2 arrays instead of 4.

=SUMIFS('Raw Data'!$K$1:$K$493665,'Raw Data'!$L$1:$L$493665,K$10,'Raw Data'!$J$1:$J$493665,YEAR(K$11),'Raw Data'!$I$1:$I$493665,MONTH(K$11),'Raw Data'!$A$1:$A$493665,$J$2,'Raw Data'!$B$1:$B$493665,$J$3,'Raw Data'!$C$1:$C$493665,$J$4,'Raw Data'!$D$1:$D$493665,$J$6,'Raw Data'!$E$1:$E$493665,$J$7,'Raw Data'!$F$1:$F$493665,$J$8,'Raw Data'!$G$1:$G$493665,$G35)

 

[cgsierra]

Given all the parameters I can't think of a way to cut it down , hopefully someone else can help.. bump

Thanks Iggy...hope so too

 

[cgsierra]

Hello Eric, given the pictures that I provided and the sumifs formula that I am using (see below), how can I expand/rewrite it to include 2 arrays? There may be a chance that I only need 2 arrays instead of 4.

=SUMIFS('Raw Data'!$K$1:$K$493665,'Raw Data'!$L$1:$L$493665,K$10,'Raw Data'!$J$1:$J$493665,YEAR(K$11),'Raw Data'!$I$1:$I$493665,MONTH(K$11),'Raw Data'!$A$1:$A$493665,$J$2,'Raw Data'!$B$1:$B$493665,$J$3,'Raw Data'!$C$1:$C$493665,$J$4,'Raw Data'!$D$1:$D$493665,$J$6,'Raw Data'!$E$1:$E$493665,$J$7,'Raw Data'!$F$1:$F$493665,$J$8,'Raw Data'!$G$1:$G$493665,$G35)

Sorry, by this I mean that the criteria array may just be needed for range J2:K8 instead of J2:M8 as highlighted in the picture above

 

[Eric W]

Based on your pictures, I can't think of any way to improve on your formula. Changing the criteria array like you mentioned really wouldn't help either. You just have a LOT of compares going on. It may be worthwhile trying a macro. How this would work would be that you would change the parameters in your criteria array, then start the macro. The macro would have the ranges of your accounts (G35 to G whatever), and dates (K10:V11, AD10:AG11, etc.). Then it would calculate the value for each intersection and insert it.

If that's of interest, let me know.

 

[cgsierra]

Based on your pictures, I can't think of any way to improve on your formula. Changing the criteria array like you mentioned really wouldn't help either. You just have a LOT of compares going on. It may be worthwhile trying a macro. How this would work would be that you would change the parameters in your criteria array, then start the macro. The macro would have the ranges of your accounts (G35 to G whatever), and dates (K10:V11, AD10:AG11, etc.). Then it would calculate the value for each intersection and insert it.

If that's of interest, let me know.

Thank you,
I think I will stick to the sumif formula that I have. However, in order to improve the processing speed of the file, I will need to hard code all formulas in the Prior Year, and Budget sections (starting in column AD). Nonetheless, it would be great to have the functionality of typing an x at the top of each column (row 9) and having a macro run to copy cells k15 though K600 to the columns where it finds the x. I would then create a second macro (for a different button) to hard code the columns marked with x. This way, any changes in formulas in Actuals 2018 would be copied over and re hardcoded at the press of two buttons

Below is the print screen for reference. I would really appreciate it if you could help me out with these 2 macros.

 

[cgsierra]

Thank you,
I think I will stick to the sumif formula that I have. However, in order to improve the processing speed of the file, I will need to hard code all formulas in the Prior Year, and Budget sections (starting in column AD). Nonetheless, it would be great to have the functionality of typing an x at the top of each column (row 9) and having a macro run to copy cells k15 though K600 to the columns where it finds the x. I would then create a second macro (for a different button) to hard code the columns marked with x. This way, any changes in formulas in Actuals 2018 would be copied over and re hardcoded at the press of two buttons

Below is the print screen for reference. I would really appreciate it if you could help me out with these 2 macros.

Hi, please let me know if you need additional information.
thank you kindly

 

[Eric W]

I had already played around with writing a macro that creates the totals. And if I read your latest posts correctly, it pretty much does what you request there too. I based it on your screen prints, which was difficult. Next time consider using one of the screen printing tools, like the HTML Maker in my signature, to post a sample of your spreadsheet. It's MUCH easier to work on a problem when you can copy the data from the post and paste it to a test workbook. Most of the time people will not bother typing in all that data, so you don't get as many people trying to answer your question. Another option is to save your workbook to a file sharing service like Dropbox and post a link, but some people still can't or won't open files from the internet.

But back to the topic at hand. Try this: Open a COPY of your workbook. Press Alt-F11 to open the VBA editor. From the menu select Insert > Module. Paste the following code into the window that opens:

Sub AddEmUp()
Dim MyTable As Range, MyAccts As Range, MyDates As Range, MyParms As Range, MyParmCols As Variant
Dim MyData As Variant, MyParmData As Variant, MyFilter As Object
Dim i As Long, j As Long, k As Long, d1 As String, acct As Variant, acc As Long, mdate As Variant


' Define parameters
    Set MyTable = Sheets("Raw Data").Range("A1:M1")             ' Define top row of the data table, the macro figures -
                                                                '  out the bottom row based on the last non-empty cell in A
    Set MyAccts = Sheets("Sheet2").Range("G35:G37")             ' This should be the column where the accounts are -
                                                                '  set the rows to first row with an account to the -
                                                                '  last row with an account, empty cells will be ignored
    Set MyDates = Sheets("Sheet2").Range("K11:M11, O11:Q11")    ' Set this to the cells with the dates, if there are gaps, -
                                                                '  define the ranges as shown.  The "ACTUALS" or "BUDGET" -
                                                                '  row is assumed to be above this row
    Set MyParms = Sheets("Sheet2").Range("J2:M8")               ' The parameters - can be an actual parameter or a "*" to mean -
                                                                '  match anything - no other wildcards are supported
    MyParmCols = Array(1, 2, 3, 0, 4, 5, 6)                     ' The columns that the parameters relate to - in this example, -
                                                                '  the parameters on row 2 match column 1 (A) on the "Raw Data" -
                                                                '  sheet, row 3 matches column 2, row 4 matches column 3, row 5 -
                                                                '  matches nothing (0), etc.
    
' Read the "Raw Data"
    MyData = MyTable.Resize(MyTable.Resize(1, 1).Offset(Rows.Count - 1).End(xlUp).Row).Value
' Read the parms
    MyParmData = MyParms.Value
' Create a dictionary to put the totals in
    Set MyFilter = CreateObject("Scripting.Dictionary")
    
' Read through the raw data, selecting the rows that match the parameters
    For i = 1 To UBound(MyData)
    
            For j = 1 To MyParms.Rows.Count
                If MyParmCols(j - 1) = 0 Then GoTo NextJ:
                For k = 1 To 4
                    If MyData(i, MyParmCols(j - 1)) = MyParms(j, k) Or MyParms(j, k) = "*" Then GoTo NextJ:
                Next k
                GoTo NextI:
NextJ:
            Next j
' Found a match on all parameters, create a key with the acct, month, year, and type,then add up the total
            d1 = CLng(MyData(i, 7)) & "|" & MyData(i, 9) & "|" & MyData(i, 10) & "|" & LCase(MyData(i, 12))
            MyFilter(d1) = MyFilter(d1) + MyData(i, 11)
            
NextI:
    Next i
    
' All totals found, now read through all the accounts/dates on the output sheet and place the totals
    Application.ScreenUpdating = False
    For Each acct In MyAccts
        acc = acct.Value
        If acc <> 0 Then                ' ignore empty cells in the Accounts column
            For Each mdate In MyDates
' Make a key with the account, month, year, and type, and read the total from the dictionary
                d1 = CLng(acct) & "|" & Month(mdate) & "|" & Year(mdate) & "|" & LCase(CStr(mdate.Offset(-1)))
                Cells(acct.Row, mdate.Column) = MyFilter(d1)
            Next mdate
        End If
    Next acct
    Application.ScreenUpdating = True
    
End Sub

Change the values in red to match your sheet. Return to your workbook. Select the output page (Sheet2). Press Alt-F8 for the macro selector, choose AddEmUp and click run.

This should run pretty fast. No formulas to slow down your sheet. No mucking around with Xs to select the columns you want, just change the ranges in the macro. Let me know what you think.