array values as sumif criteria in vba

A

alk3ckwd

New Member
Joined
Feb 6, 2009
Messages
9
Hi I'm trying to come up with a way to represent a formula like:

=SUM(SUMIF(A2:A10,{"a";"b"},B2:B10))

into a dynamic vba statement.

i have an array built programmaticaly through vba with a list of names for different facilities, and i want to sum the number of hours they work. So in the above it be like looking in the range A2:A10 for any of the values in my array ({"a";"b"}) and summing up their hours which are in B2:B10.

I've tried stuff like

Code: 
Sheets("Sheet1").Cells(1,1).value = Evaluate("=sum(sumif(Sheet2!C:C," & array & ",Sheet2!D:D)")

but that just gives me a #VALUE! error.

Any help would be much appreciated!
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
Hi,
Try adapting this code:
Rich (BB code): 
Sub TEST()
  Dim arr, s As String
  arr = Array("a", "b")
  s = "=SUM(SUMIF(Sheet2!C:C,{""" & Join(arr, """;""") & """},Sheet2!D:D))"
  Debug.Print s
  With Sheets("Sheet1").Cells(1, 1)
    .Formula = s
    .Value = .Value
  End With
End Sub
 
Upvote 0
Thanks, that worked great!

I did have to adjust the definition of s to get rid of the extra quotations before/after the Join function and the ; inside the Join though. With arr already having the elements as strings (and therefore having quotes around them, those extra bits were just adding to that. But anyway, thanks!
Code: 
  s = "=sum(sumif(Feb!C:C,{" & Join(phys2clin, ";") & "},Feb!D:D))"
 
Upvote 0
Glad to see you got it working and thanks for the feedback!
 
Upvote 0
ZVI said:
Glad to see you got it working and thanks for the feedback!
Click to expand...

Hello,

After quite a bit of searching I found this thread which seemed to hit the nail on the head of what I was looking for. Im trying to do a sumIf for multiple criteria in the same column. In the code below I stored the criteria in a two element array and then tried to mirror your formula above to no avail. I am receiving a Run-Time Error '1004' Application defined or Object Defined Error. Any advice you can lend would be greatly appreciated.



Code:
Select Case Left(wf.Trim(wf.Clean(LCase(cl))), 2)
Case "bp", "p1": pc1 = Left(wf.Trim(wf.Clean(LCase(cl))), 10)
pc2 = Left(wf.Trim(wf.Clean(LCase(cl))), 7) & Mid(cl, 12, 3)

arr = Array(pc1, pc2)

For Each cl1 In ws_port.Range(Cells(cl.Row, Range("curr_zfin").Offset(, -5).Column), Cells(cl.Row, Range("curr_zfin").Column))
cl1.Formula = "=Sum(SumIF('[ZFIN.xlsx]" & Cells(Range("curr_zfin").Row, cl1.Column) & "'!$B$7:$B$2500," & Join(arr, ";") & ",'[ZFIN.xlsx]" & Cells(Range("curr_zfin").Row, cl1.Column) & "'!$D$7:$D$2500))"
cl1.Value = cl1.Value * -1
cl1.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* "" - ""??_);_(@_)"
Next cl1
Code:
 
Upvote 0
winneker said:
...Any advice you can lend would be greatly appreciated.
Click to expand...
Hi,

Without knowing of your data layout and types & contents of variables – just common suggestions:

1. Select Case has to be ended with End Select
2. Use Case Else to exit subroutine for other cases
3. Build formula in the string variable and analyze (debug) it before applying to the cell

Template of the code may look like this:
Rich (BB code): 
  Select Case ...
    Case "bp", "p1"
      pc1 = ...
      pc2 = ...
    Case Else: Exit Sub  ' <-- exclude other cases to prevent formula error
  End Select
 
  ' Check values of pc1 and pc2 variables
  Debug.Print "pc1", pc1
  Debug.Print "pc2", pc2
 
  Dim MyFormula As String
 
  For Each cl1 In ...
   
    ' Construct the formula
    MyFormula = ...
   
    ' and check it
    Debug.Print cl1.Address, MyFormula
   
    ' then try to use it
    cl1.Formula = MyFormula
    cl1.Value = cl1.Value * -1
   
    ' Set format
    cl1.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* "" - ""??_);_(@_)"
   
  Next

4. Use Option Explicit statement in the top of the code module and try VBE - menu Debug – Compile VBAProject to find undeclared variables and possible syntax errors.

5. Run the code step by step by pressing F8, use VBE menu View – Locals to see details of variables,
read more about debugging in this link Chip Pearson - Debugging VBA

6. Inform what line of the code causes error if it happen

Regards,
 
Last edited:
Upvote 0
Thanks for the response. Sorry for the messy code. I used the wrap code buttons incorrectly. I also didn't post all of the declarations nor the End Select. Here is a bigger picture of my code. The error in question is on lines 86, 87 and is my attempt at the Sum(SumIf()) formula where i'd like to look up qty (5) criteria: pc1, pc2, pc3, pc4, and pc5 in my column range and return the sum of any/all of them.

Code: 
Dim wb As ThisWorkbook, ws_port As Worksheet, pth As String, wf As WorksheetFunction, wb_zfin As Workbook, cl As Range, cl1 As Range
Dim ws_1 As Worksheet, ws_2 As Worksheet, ws_3 As Worksheet, ws_4 As Worksheet, ws_5 As Worksheet, ws_6 As Worksheet, ws_7 As Worksheet, ws_8 As Worksheet, ws_9 As Worksheet, ws_10 As Worksheet
Dim ws_11 As Worksheet, ws_12 As Worksheet, ws As Worksheet, s As Variant, arr As Variant
Dim pc1 As String, pc2 As String, pc3 As String, pc4 As String, pc5 As String

Set wb = ThisWorkbook
Set wf = WorksheetFunction
Set ws_port = wb.Worksheets("Portfolio")

pth = "\\rsmca8-fil01\vol2\ATN_ORG\ATN Program Porfolio\input reports\ZFIN.xlsx"

On Error Resume Next
Set wb_zfin = Workbooks("ZFIN.xlsx")
On Error GoTo 0

If wb_zfin Is Nothing Then
    Set wb_zfin = Workbooks.Open(Filename:=pth, ReadOnly:=True)
End If

Set ws_1 = wb_zfin.Worksheets("1")
Set ws_2 = wb_zfin.Worksheets("2")
Set ws_3 = wb_zfin.Worksheets("3")
Set ws_4 = wb_zfin.Worksheets("4")
Set ws_5 = wb_zfin.Worksheets("5")
Set ws_6 = wb_zfin.Worksheets("6")
Set ws_7 = wb_zfin.Worksheets("7")
Set ws_8 = wb_zfin.Worksheets("8")
Set ws_9 = wb_zfin.Worksheets("9")
Set ws_10 = wb_zfin.Worksheets("10")
Set ws_11 = wb_zfin.Worksheets("11")
Set ws_12 = wb_zfin.Worksheets("12")

For Each ws In wb_zfin.Worksheets
    ws.Range("B1").EntireColumn.Insert
    ws.Range("B1").EntireColumn.NumberFormat = "General"
    lastrow = ws.Cells(Rows.Count, 3).End(xlUp).Row
    For Each cl In ws.Range("C7:C" & lastrow)
        Select Case Left(cl, 6)
            Case "      ":  cl.Offset(0, -1).Value = Mid(cl.Value, 7, 10)
            Case "**    ":  Select Case Mid(cl, 7, 2)
                                Case "IS": cl.Offset(0, -1).Value = "T3-" & Mid(cl, 9, 5)
                                Case "MS": cl.Offset(0, -1).Value = "R1-" & Mid(cl, 9, 5)
                            End Select
            Case "***   ":  s = Split(cl, " ")
                            cl.Offset(0, -1).Value = wf.Trim(wf.Clean(s(3)))
            Case Else:
        End Select
    Next cl
Next ws

wb.Activate

Application.ScreenUpdating = False

For Each cl In ws_port.Range("curr_month").Offset(3, 0).Resize(Cells(Rows.Count, 2).End(xlUp).Row - 3, 1)
    Select Case Len(wf.Trim(wf.Clean(LCase(cl))))
        Case 10: Select Case Left(wf.Trim(wf.Clean(LCase(cl))), 2)
                 Case "bp", "p1": Select Case Right(wf.Trim(wf.Clean(LCase(cl))), 3)
                                  Case "all": For Each cl1 In ws_port.Range(Cells(cl.Row, Range("curr_zfin").Offset(, -5).Column), Cells(cl.Row, Range("curr_zfin").Column))
                                                cl1.Formula = "=IF(ISNA(Vlookup($C" & cl.Row & ",'[ZFIN.xlsx]" & Cells(Range("curr_zfin").Row, cl1.Column) & "'!$B:$L,3,False)),0,Vlookup($C" & cl.Row & ",'[ZFIN.xlsx]" & Cells(Range("curr_zfin").Row, cl1.Column) & "'!$B:$L,3,False))"
                                                cl1.Value = cl1.Value * -1
                                                cl1.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* "" - ""??_);_(@_)"
                                              Next cl1
                                  Case Else:  For Each cl1 In ws_port.Range(Cells(cl.Row, Range("curr_zfin").Offset(0, -5).Column), Cells(cl.Row, Range("curr_zfin").Column))
                                                cl1.Formula = "=IF(ISNA(Vlookup($B" & cl.Row & ",'[ZFIN.xlsx]" & Cells(Range("curr_zfin").Row, cl1.Column) & "'!$B:$L,3,False)),0,Vlookup($B" & cl.Row & ",'[ZFIN.xlsx]" & Cells(Range("curr_zfin").Row, cl1.Column) & "'!$B:$L,3,False))"
                                                cl1.Value = cl1.Value * -1
                                                cl1.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* "" - ""??_);_(@_)"
                                              Next cl1
                                  End Select
                 Case Else: For Each cl1 In ws_port.Range(Cells(cl.Row, Range("curr_zfin").Offset(, -5).Column), Cells(cl.Row, Range("curr_zfin").Column))
                              cl1.Formula = "=IF(ISNA(Vlookup($B" & cl.Row & ",'[ZFIN.xlsx]" & Cells(Range("curr_zfin").Row, cl1.Column) & "'!$B:$L,3,False)),0,Vlookup($B" & cl.Row & ",'[ZFIN.xlsx]" & Cells(Range("curr_zfin").Row, cl1.Column) & "'!$B:$L,3,False))"
                              cl1.Value = cl1.Value * -1
                              cl1.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* "" - ""??_);_(@_)"
                            Next cl1
                 End Select
        Case 14: Select Case Left(wf.Trim(wf.Clean(LCase(cl))), 2)
                             Case "bp", "p1": pc1 = Left(wf.Trim(wf.Clean(LCase(cl))), 10)
                                              pc2 = Left(wf.Trim(wf.Clean(LCase(cl))), 7) & Mid(cl, 12, 3)
                                                        
                                              arr = Array(pc1, pc2)
                                                        
                                              For Each cl1 In ws_port.Range(Cells(cl.Row, Range("curr_zfin").Offset(, -5).Column), Cells(cl.Row, Range("curr_zfin").Column))
                                                cl1.Formula = "=Sum(SumIF('[ZFIN.xlsx]" & Cells(Range("curr_zfin").Row, cl1.Column) & "'!$B$7:$B$2500," & Join(arr, ";") & ",'[ZFIN.xlsx]" & Cells(Range("curr_zfin").Row, cl1.Column) & "'!$D$7:$D$2500))"
                                                cl1.Value = cl1.Value * -1
                                                cl1.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* "" - ""??_);_(@_)"
                                              Next cl1
        
                             Case Else: For Each cl1 In ws_port.Range(Cells(cl.Row, Range("curr_zfin").Offset(, -5).Column), Cells(cl.Row, Range("curr_zfin").Column))
                                          cl1.Formula = "=IF(ISNA(Vlookup($B" & cl.Row & ",'[ZFIN.xlsx]" & Cells(Range("curr_zfin").Row, cl1.Column) & "'!$B:$L,3,False)),0,Vlookup($B" & cl.Row & ",'[ZFIN.xlsx]" & Cells(Range("curr_zfin").Row, cl1.Column) & "'!$B:$L,3,False))"
                                          cl1.Value = cl1.Value * -1
                                          cl1.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* "" - ""??_);_(@_)"
                                        Next cl1
                             End Select
        'Case 14, 18, 22, 26: Select Case Left(wf.Trim(wf.Clean(LCase(cl))), 2)
                             'Case "bp", "p1": pc1 = Left(wf.Trim(wf.Clean(LCase(cl))), 10)
                                              'pc2 = Left(wf.Trim(wf.Clean(LCase(cl))), 7) & Mid(cl, 12, 3)
                                              'pc3 = Left(wf.Trim(wf.Clean(LCase(cl))), 7) & Mid(cl, 16, 3)
                                              'pc4 = Left(wf.Trim(wf.Clean(LCase(cl))), 7) & Mid(cl, 20, 3)
                                              'pc5 = Left(wf.Trim(wf.Clean(LCase(cl))), 7) & Mid(cl, 24, 3)
                                                        
                                              'arr = Array(pc1, pc2, pc3, pc4, pc5)
                                                        
                                              'For Each cl1 In ws_port.Range(Cells(cl.Row, Range("curr_zfin").Offset(, -5).Column), Cells(cl.Row, Range("curr_zfin").Column))
                                                'cl1.Formula = "=Sum(SumIF('[ZFIN.xlsx]" & Cells(Range("curr_zfin").Row, cl1.Column) & "'!$B$7:$B$2500,{" & Join(arr, ";") & "},'[ZFIN.xlsx]" & Cells(Range("curr_zfin").Row, cl1.Column) & "'!$D$7:$D$2500))"
                                                'cl1.Value = cl1.Value * -1
                                                'cl1.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* "" - ""??_);_(@_)"
                                              'Next cl1
        
                             'Case Else: For Each cl1 In ws_port.Range(Cells(cl.Row, Range("curr_zfin").Offset(, -5).Column), Cells(cl.Row, Range("curr_zfin").Column))
                                          'cl1.Formula = "=IF(ISNA(Vlookup($B" & cl.Row & ",'[ZFIN.xlsx]" & Cells(Range("curr_zfin").Row, cl1.Column) & "'!$B:$L,3,False)),0,Vlookup($B" & cl.Row & ",'[ZFIN.xlsx]" & Cells(Range("curr_zfin").Row, cl1.Column) & "'!$B:$L,3,False))"
                                          'cl1.Value = cl1.Value * -1
                                          'cl1.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* "" - ""??_);_(@_)"
                                        'Next cl1
                             'End Select
        Case Else: For Each cl1 In ws_port.Range(Cells(cl.Row, Range("curr_zfin").Offset(, -5).Column), Cells(cl.Row, Range("curr_zfin").Column))
                     cl1.Formula = "=IF(ISNA(Vlookup($B" & cl.Row & ",'[ZFIN.xlsx]" & Cells(Range("curr_zfin").Row, cl1.Column) & "'!$B:$L,3,False)),0,Vlookup($B" & cl.Row & ",'[ZFIN.xlsx]" & Cells(Range("curr_zfin").Row, cl1.Column) & "'!$B:$L,3,False))"
                     cl1.Value = cl1.Value * -1
                     cl1.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* "" - ""??_);_(@_)"
                   Next cl1
    End Select
Next cl

Application.ScreenUpdating = True

wb_zfin.Close SaveChanges:=False

End Sub
 
Upvote 0
If code does not work then debugging is necessary to find the reason of a problem.

Try point #3 of my previous post #6.
That is, replace this part of the code:
Rich (BB code): 
  For Each cl1 In ws_port.Range(Cells(cl.Row, Range("curr_zfin").Offset(, -5).Column), Cells(cl.Row, Range("curr_zfin").Column))
    cl1.Formula = "=Sum(SumIF('[ZFIN.xlsx]" & Cells(Range("curr_zfin").Row, cl1.Column) & "'!$B$7:$B$2500," & Join(arr, ";") & ",'[ZFIN.xlsx]" & Cells(Range("curr_zfin").Row, cl1.Column) & "'!$D$7:$D$2500))"
    cl1.Value = cl1.Value * -1
    cl1.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* "" - ""??_);_(@_)"
  Next cl1

by that one:
Rich (BB code): 
  Dim MyFormula As String ' <-- variable for the formula
  For Each cl1 In ws_port.Range(Cells(cl.Row, Range("curr_zfin").Offset(, -5).Column), Cells(cl.Row, Range("curr_zfin").Column))
    MyFormula = "=Sum(SumIF('[ZFIN.xlsx]" & Cells(Range("curr_zfin").Row, cl1.Column) & "'!$B$7:$B$2500," & Join(arr, ";") & ",'[ZFIN.xlsx]" & Cells(Range("curr_zfin").Row, cl1.Column) & "'!$D$7:$D$2500))"
    Debug.Print cl1.Address, MyFormula  ' <-- compare what is printed in Immediate window vs what is expected
    cl1.Formula = MyFormula
    cl1.Value = cl1.Value * -1
    cl1.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* "" - ""??_);_(@_)"
  Next cl1

Run the code (point #5) and after displaying in Immediate window of the constructed formula compare it with what it have to be.

You may copy formula from Immediate window and paste it into the appropriate cell (its address is in Immediate window too).
Then in Excel find out what is wrong in that formula and fix the code accordingly.
 
Last edited:
Upvote 0
If debugger highlights this line of the code (it's not clear from number of line you have posted because top line of the Sub() is not present in the code):
Rich (BB code): 
For Each cl1 In ws_port.Range(Cells(cl.Row, Range("curr_zfin").Offset(, -5).Column), Cells(cl.Row, Range("curr_zfin").Column))
Then the problem is in fact ws_port is not the active sheet and in this case Cells(... , ...) belongs to another (active) sheet.

The fixed line of the code:
Rich (BB code): 
For Each cl1 In ws_port.Range(ws_port.Cells(cl.Row, Range("curr_zfin").Offset(, -5).Column), ws_port.Cells(cl.Row, Range("curr_zfin").Column))
 
Last edited:
Upvote 0
Thanks very much ZVI for the help and quick turnaround. Using your recommended Debug.Print Address, myformula I was able to confirm the address was working properly but after looking at the formula in the immediate window I noticed that I left off the four sets of double quotes in the joining of my array elements. I know those were noted in your first response above, and then alk3ckwd said they removed those, as did I. After adding back in the double quote sets it works perfectly. Problem solved! thanks again!
 
Upvote 0
You must log in or register to reply here.

Similar threads

Richard1982
  • Solved
=SUMIF() question : can the criteria be a range of things?
Replies
9
Views
595
arthurbr
arthurbr
M
  • Solved
Sum only the unique values from one column within a range of duplicate values from another column
Replies
7
Views
146
MARZIOTULLIO
M
D
  • Solved
SUMIFS: array for the exclude criteria
Replies
3
Views
432
Georgiboy
Georgiboy
B
  • Solved
VBA to check if worksheet exists
Replies
8
Views
258
bdt
B
F
  • Solved
VBA change event applied to one or multiple cells
Replies
3
Views
341
FryGirl
F

Forum statistics

Threads
1,223,240
Messages
6,170,951
Members
452,368
Latest member
jayp2104

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top