sum if 2 criteria

[oleg_v]

HI
I need a help with a macro

Sub s_Product_CAPACITY()
     
    Dim rng1 As String, rng2 As String, rng3 As String, mnth As String
    Dim Str1 As String, str2 As String, spFormula As String
    Dim res As Variant
    Dim i As Integer
    Dim H As Integer
    k = 1
    On Error GoTo fg
    Y = Sheets("Year Count").Range("AD65536").End(xlUp).Row
fg:
   
  For n = 2 To Y
  k = k + 1
    
    rng1 = "'year'!U2:U65536"
    rng2 = "'year'!A2:A65536"
    rng3 = "'year'!E2:E65536"
    
     
    Str1 = Sheets("Year Count").cells(n, "AD").Value
    str2 = Sheets("Year Count").cells(3, "Z").Value
     
    spFormula = "SumProduct((" & rng1 & "=""" & Str1 & """)*(" & rng2 & "=""" & str2 & """)*(" & rng3 & "))"
    
    res = Application.Evaluate(spFormula)
     
    If Not IsError(res) Then Sheets("Year Count").cells(k, "AE").Value = res
    
     Next n
     
End Sub

i need that "str1" and "str2" will note the hole string i need it to be part of the string in the cell

thanks

 

[DonkeyOte]

Are you saying you want a partial match of str1 and str2 rather than complete ?

Assuming that to be the case:

spFormula = "SUMPRODUCT(ISNUMBER(SEARCH(""" & Str1 & """," & rng1 & "))*ISNUMBER(SEARCH(""" & Str2 & """," & rng2 & "))," & rng3 & ")"

try to avoid using excessive precedent ranges with SUMPRODUCT if you can - it is not efficient.

Depending on consistency of delimiters within the strings you might choose to adjust the above accordingly so as to reduce risk of false positives.
For case sensitive version of the same (apple <> Apple) revert to FIND

 

[oleg_v]

Thanks