Sumif

[oleg_v]

Hi
i need some help.
how can i write a macro for "sumif" with 2 or more criterias.
at this moment i have a macro that ansvers only to 1 criteria
Sub SUMIFCOMPLETE()
Dim Sumact As Long
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim I As Long
Dim AT As String
Dim clumn As Long
Dim Lastrow As Long
DT = Sheets("Year Count").cells(4, "Z").Value
For I = 1 To 12
Column = I + 12
Sumact = Application.SumIf(Sheets("p" & I).Range("c2:c30000"), "*" & DT & "*", Sheets("p" & I).Range("j2:j30000"))
Sheets("Year Count").cells(6, Column).Value = Sumact

Next I
End Sub

thanks

[mancubus]

hi.
you can use sumproduct for multiple condition sum.

see the sticky thread in excel help forum.

[oleg_v]

thanks

is sumproduct i can use in macro please advice
where issticky thread "

i need a macro to do the calculattion

[mancubus]

click forum excel help and scroll up to the top of the page.

or
http://www.vbaexpress.com/forum/forumdisplay.php?f=98

here is an example for vba.


Sub s_Product()
Dim rA As Range, rB As Range, rC As Range, rD As Range
Dim str1 As String, str2 As String, str3 As String
Dim sum_result As Variant

Set rA = Sheets("Sheet1").Range("A1:A300")
Set rB = Sheets("Sheet1").Range("B1:B300")
Set rC = Sheets("Sheet1").Range("C1:C300")
Set rD = Sheets("Sheet1").Range("D1:D300")

str1 = "Crit1"
str2 = "Crit2"
str3 = "Crit3"

sum_result = WorksheetFunction.SumProduct((rA.Value = str1) * _
(rB.Value = str2) * (rC.Value = str3) * rD.Value))

End Sub

[oleg_v]

hi
thanks
i am getting an error type mismach
the code i used;

Sub s_Product()
Dim rA As Range, rB As Range, rC As Range, rD As Range
Dim str1 As String, str2 As String, str3 As String
Dim sum_result As Variant

Set rA = Sheets("year").Range("u2:u5000")
Set rB = Sheets("year").Range("p2:p5000")
'Set rC = Sheets("year").Range("C1:C300")
Set rD = Sheets("year").Range("e2:e5000")

str1 = "January"
str2 = "wiring"
' str3 = "Crit3"

sum_result = WorksheetFunction.SumProduct((rA.Value = str1) * (rB.Value = str2) * rD.Value)
Sheets("SHEET1").cells("a2").Value = sum_result

End Sub

[mancubus]

string. :banghead:

pls let me know if below code produces any errors.

str1 = """January"""
str2 = """wiring"""

[mancubus]

sum_result = Application.Evaluate("SUMPRODUCT((year!U2:U500=""January"")*(YEAR!P2:P500=""wiring"")*(year!E2:E500))")
Sheets("SHEET1").Cells("a2").Value = sum_result

[mancubus]

one more, but last try.

i'm lost in the "labyrinth of the single and double quotes" . :help :rofl:

credits:
http://www.vbaexpress.com/forum/showthread.php?t=18436
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://www.mrexcel.com/forum/showthread.php?t=65622


Sub s_Product()

Dim rng1 As Range, rng2 As Range, rng3 As Range
Dim str1 As String, str2 As String, spFormula As String

Set rng1 = Sheets("year").Range("U2:U5000")
Set rng2 = Sheets("year").Range("P2:P5000")
Set rng3 = Sheets("year").Range("E2:E5000")

str1 = "January"
str2 = "wiring"

spFormula = "SumProduct((" & rng1.Address & "= """ & str1 & """) * " & _
"(" & rng2.Address & "= """ & str2 & """) * " & "(" & rng3.Address & "))"

Sheets("Sheet1").Range("E2").Value = Application.Evaluate(spFormula)

End Sub

[oleg_v]

Hi
thanks
it works without an errors but the result
gives me "#VALUE!"
what can i do please advice?

thatks a lot for your effort

[mancubus]

trying...

[oleg_v]

Thanks
i am looking forward for your answer.

[mancubus]

try below. if not works, pls upload your file with representative / fake data.


spFormula = "SUMPRODUCT(--(" & rng1.Address & "=""" & str1 & """),(" & rng2.Address & "=""" & str2 & """),(" & rng3.Address & "))"

[oleg_v]

HI
the result gives me "0"
attached file like you requested.


thanks Oleg

[mancubus]

your sample file does not contain the original crits, so i changed.
worked for me.

based:
col U = text
col P = text
col E = number


Sub s_Product_fin()

Dim rng1 As String, rng2 As String, rng3 As String
Dim str1 As String, str2 As String, spFormula As String
Dim res As Variant

rng1 = "'year'!U2:U65536"
rng2 = "'year'!P2:P65536"
rng3 = "'year'!E2:E65536"

str1 = "August"
str2 = "Industrial"

spFormula = "SumProduct((" & rng1 & "=""" & str1 & """)*(" & rng2 & "=""" & str2 & """)*(" & rng3 & "))"

res = Application.Evaluate(spFormula)

If Not IsError(res) Then Sheets("Sheet1").Range("A2") = res

End Sub

[oleg_v]

hi
i am trying to use this macro for the differant file and it is gives me a 0 answer please
i need some help to understend what wrong

thanks attache file