Urgent - SumIf being stubborn!

[Wolfen02]

I'm using the following formula in VBA to try to get the number of symbols in Range2 where Range1 is null. There are 5 possible symbols to be found in either range. The formula will only return zero. Where am I going wrong?!?

s = Application.WorksheetFunction.SumIf(Range1, "=" """",Range2)

Teri
Good decisions come from Wisdom...
Wisdom comes from experience...
Experience comes from bad decisions

 

[PHV]

SumIf(Range1, "="" """,Range2)


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Wolfen02]

PHV,
Formula still only returns zero. Thanks for the try though.


Teri
Good decisions come from Wisdom...
Wisdom comes from experience...
Experience comes from bad decisions

 

[Wolfen02]

Solved my issue by looping along the If statements. Slower, but works!

Teri
Good decisions come from Wisdom...
Wisdom comes from experience...
Experience comes from bad decisions

 

[KenWright]

How about:-

Hardwired Range

Sub SumPr1()
    Dim wks As Worksheet
    Set wks = Worksheets("sheet1")
    
    With wks
      myval = Evaluate("SUMPRODUCT((" _
              & .Range("A1:A30").Address(external:=True) & "="""") *(" _
              & .Range("B1:B30").Address(external:=True) & "<>""""" _
              & "))")
    End With
    
    MsgBox myval
    
End Sub

or

Sub SumPr2()
    Dim wks As Worksheet
    Dim rng1 As Range
    Dim rng2 As Range
        
    Set wks = Worksheets("sheet1")
    
    With wks
      Set rng1 = .Range("A1:A30")
      Set rng2 = .Range("B1:B30")
    
      myval = Evaluate("SUMPRODUCT((" _
              & rng1.Address(external:=True) & "="""") *(" _
              & rng2.Address(external:=True) & "<>""""" _
              & "))")
    End With
    
    MsgBox myval
    
End Sub

Regards
Ken.................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[byundt]

The following function adds up the values in range2 in rows where range1 is blank.

Function mySumIF(range1 As Range, range2 As Range)
mySumIF = Application.SumIf(range1, "=" & "", range2)
End Function

Note that it is comparing the values in range1 to

 

[KenWright]

Hi byundt - I think you can even lose the '"=" &' bit can't you:-

Function mySumIF(range1 As Range, range2 As Range)
    mySumIF = Application.SumIf(range1, "", range2)
End Function

Problem is though that I think the OP is looking to count disparate symbols, and if he has 5 of them then any summation of values (if indeed they are even values) will likely not be able to get a valid count for him.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------