sumif with multiple criteria

[Akbarov]

Hey community,

i use following VBA

Sub BonaquaASM1()Application.ScreenUpdating = False
    Dim Markets As Worksheet, lr As Long
    lr = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
    Set Markets = Sheets("sheet4")
    Sheets("DATA").Range("A1:A" & lr).Name = "run"
    Sheets("DATA").Range("L1:L" & lr).Name = "sifaris"
    Sheets("DATA").Range("M1:M" & lr).Name = "inted"
    Sheets("DATA").Range("E1:E" & lr).Name = "teri"
    Sheets("DATA").Range("H1:H" & lr).Name = "asm"
    Sheets("sheet4").Range("AP1:AP" & lr).Name = "ayi"
    Sheets("sheet4").Cells(1, 44).Name = "asma"
    Markets.Range("c1:c" & lr).Name = "MARKET"
    With Sheets("DATA").Cells(2, "BB")
    .FormulaArray = "=sum(if((isnumber(match(run,market,0)))*(sifaris>0)*(urun<>"""")*(not(isnumber(match(teri,ayi,0))))*(isnumber(match(asm,asma,0))),inted))"
    .Value = .Value
End With
End Sub

formula was working fine, i only added last part

*(isnumber(match(asm,asma,0)))

now i get 0 result. what am i doing wrong? i need it to sum if asm=asma

 

[Akbarov]

any kind of help please?

 

[DanteAmor]

The name "urun" is not defined in the code like the other names.

.FormulaArray = "=sum(if((isnumber(match(run,market,0)))*(sifaris>0)*([COLOR=#ff0000]urun[/COLOR]<>"""")*(not(isnumber(match(teri,ayi,0))))*(isnumber(match(asm,asma,0))),inted))"
    .Value = .Value

 

[Akbarov]

i'v edited that, but thats not solved problem, can you help me to change this formula to SUMIFS?

 

[DanteAmor]

In this line you are not indicating which sheet.

[COLOR=#333333]lr = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row[/COLOR]

You should have 2 variables one for sheet4 and one for "data" sheet

Sub BonaquaASM1()
  Application.ScreenUpdating = False
  Dim Markets As Worksheet, [B][COLOR=#0000FF]lr1 [/COLOR][/B]As Long, [B][COLOR=#FF0000]lr2[/COLOR][/B] As Long
  Set Markets = Sheets("sheet4")
  [B][COLOR=#0000ff]lr1 [/COLOR][/B]= Sheets("[COLOR=#008000]sheet4[/COLOR]").Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
  [B][COLOR=#ff0000]lr2 [/COLOR][/B]= Sheets("[COLOR=#b22222]DATA[/COLOR]").Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
  Sheets("DATA").Range("A1:A" & [B][COLOR=#FF0000]lr2[/COLOR][/B]).Name = "run"
  Sheets("DATA").Range("L1:L" & [B][COLOR=#FF0000]lr2[/COLOR][/B]).Name = "sifaris"
  Sheets("DATA").Range("M1:M" & [B][COLOR=#FF0000]lr2[/COLOR][/B]).Name = "inted"
  Sheets("DATA").Range("E1:E" & [B][COLOR=#FF0000]lr2[/COLOR][/B]).Name = "teri"
  Sheets("DATA").Range("H1:H" & [B][COLOR=#FF0000]lr2[/COLOR][/B]).Name = "asm"
  Sheets("sheet4").Range("AP1:AP" & [B][COLOR=#0000FF]lr1[/COLOR][/B][COLOR=#0000ff])[/COLOR].Name = "ayi"
  Sheets("sheet4").Cells(1, 44).Name = "asma"
  Markets.Range("c1:c" & [B][COLOR=#0000FF]lr1[/COLOR][/B]).Name = "MARKET"
  With Sheets("DATA").Cells(2, "BB")
    .FormulaArray = "=sum(if((isnumber(match(run,market,0)))*(sifaris>0)*(run<>"""")*(not(isnumber(match(teri,ayi,0))))*(isnumber(match(asm,asma,0))),inted))"
    .Value = .Value
  End With
End Sub

 

[Akbarov]

Thanks for reply, i tryed your work. it only works when i delete

(isnumber(match(asm,asma,0)))

this part

 

[DanteAmor]

It works with my test data, I would have to review your data.

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[Akbarov]

yes it contains confidential information , asma is numerical values ( sales persons ID : 370 in this case ) , and asm contains full list with all ids, i need VBA to sum if 370 is on list

i would be really gratefull if you can help me to change this formula to SUMIFS if possible, thisone seems very complicated for me

 

[Akbarov]

it works, problem was because i took data from server, numbers saved as texts, i fixed it now it works fine, thank you very much

 

[DanteAmor]

it works, problem was because i took data from server, numbers saved as texts, i fixed it now it works fine, thank you very much

I'm glad to help you. Thanks for the feedback.