SumIfs with multiple criteria using VBA

Hi all

I have the following code to sum cost in column J if the following criteria is met:

1 If the specified GLCode is in column P
2 If the specified start date (FromDate) is in column L
3 If the specified end date (ToDate) is also in colmn L
4 If the specifiedfile name (in this instance MasterFile_00) is in column Z

My problem is that I need increase the number of criteria 4 to about 20 files to check (MasterFile_12, MasterFile_19, MasterFile_34 and so on).

How could I amend the code to check occurance of these file names for inculding in the sumifs formula?

Code :

DateNow = Date

    FromDate = DateSerial(Year(DateNow), Month(DateNow), 1)

    ToDate = DateSerial(Year(DateNow), Month(DateNow) + 1, 0)
     
    InMonth = FormatCurrency(Application.WorksheetFunction.SumIfs(.Range("J:J"), _
                                                                        .Range("P:P"), GLCode, _
                                                                        .Range("L:L"), ">=" & FromDate, _
                                                                        .Range("L:L"), "<" & ToDate, _
                                                                        .Range("Z:Z"), "MasterFile_00"))

Many thanks

oeldere

Thank you for your interest.

I have a summary workbook which has all the data.

The formula is trying to sum all money spent by different departments (MasterFile_00, MasterFile_12 etc) for different cost centres (GLCode).

When I tried the previous code I sent, it gave me the correct answer for money spent by department MasterFile_00. However, I want to check all money spent by departments MasterFile_00 and MasterFile_13 and tried this code which gave me a zero answer:

DateNow = Date
    
    FromDate = DateSerial(Year(DateNow), Month(DateNow), 1): ToDate = DateSerial(Year(DateNow), Month(DateNow) + 1, 0)
     
    InMonth = FormatCurrency(Application.WorksheetFunction.SumIfs(.Range("J:J"), _
                                                                        .Range("P:P"), GLCode, _
                                                                        .Range("L:L"), ">=" & FromDate, _
                                                                        .Range("L:L"), "<" & ToDate, _
                                                                        .Range("Z:Z"), "MasterFile_00", _
                                                                        .Range("Z:Z"), "MasterFile_013"))

How can I sumif with more cost centres. Maybe sumifs is not the right way?

Thanks

Hi again oeldere

I attach a sample worksheet which gives the desired result when I use standard formula in cells L37 and L38.

I summed the sumifs, so maybe this is the way to do in VBA? The problem is that there will be up to 20 sumifs because there are 20 different Cost Centres to deal with! Would there be a simple way to do multiple sumifs rather than write each out 20 times? (...or should this be another thread started?)

Thanks

Hi Debraj Roy...your formula works great in a worksheet, but I can't seem to adapt that to VBA. How can I do that? I know you have used arrays, but I do not know how to put it in VBA. Could you advise?

Thank you both for your help

I used this code to get what I wanted, but will have to repeat it many times with other cost centres to capture all the data:

DateNow = Date
    FromDate = DateSerial(Year(DateNow), Month(DateNow), 1): ToDate = DateSerial(Year(DateNow), Month(DateNow) + 1, 0)
     
    InMonth = FormatCurrency(Application.WorksheetFunction.SumIfs(.Range("J:J"), _
                                                                        .Range("P:P"), GLCode, _
                                                                        .Range("L:L"), ">=" & FromDate, _
                                                                        .Range("L:L"), "<" & ToDate, _
                                                                        .Range("Z:Z"), "MasterFile_00") + _
                                Application.WorksheetFunction.SumIfs(.Range("J:J"), _
                                                                        .Range("P:P"), GLCode, _
                                                                        .Range("L:L"), ">=" & FromDate, _
                                                                        .Range("L:L"), "<" & ToDate, _
                                                                        .Range("Z:Z"), "MasterFile_01"))

Debraj Roy, your formulae has increased my knowledge in using arrays. Thanks

oeldere, thanks for showing how to create a pivot table using VBA. I will probably use that approach rather than using my code.

Cheers

Hi again Debraj

I changed the code to your suggested one:

InMonth = FormatCurrency(Evaluate("=Sum(SumIfs(.Range(J:J), .Range(P:P), GLCode, .Range(L:L), " >= " & FromDate, .Range(L:L), " < " & ToDate, .Range(Z:Z), ""MasterFile_00"",""MasterFile_01""}))"))

but it is giving a Type Mismatch error. Have I missed some quotes in this?

Cheers

Hi Debraj. I am a newbie when it comes to VBA coding, so haven't used Set FromDate etc. I have done it the long winded way! The full code for that part is:

    Dim DateNow As Double, FromDate As Double, ToDate As Double
    
    With Sheets(strSheetName)
    
    SpentYTD = FormatCurrency(Evaluate("=Sum(SumIfs(J:J, P:P, GLCode, Z:Z, {""MasterFile_00"",""MasterFile_01""}))"))
    
          DateNow = Date
    
          FromDate = DateSerial(Year(DateNow), Month(DateNow), 1): ToDate = DateSerial(Year(DateNow), Month(DateNow) + 1, 0)
     
    InMonth = FormatCurrency(Evaluate("=Sum(SumIfs(J:J, P:P, GLCode, L:L, "" >= & "" FromDate, L:L, "" < &"" _
                                                    ToDate, Z:Z, ""MasterFile_00"",""MasterFile_01""}))"))
End With

I amended the code as you suggested, but it doesn't work. I am obviously missing something.

Thanks