View Full Version : Sumif
oleg_v
02-07-2011, 04:24 AM
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
02-07-2011, 05:04 AM
hi.
you can use sumproduct for multiple condition sum.
see the sticky thread in excel help forum.
oleg_v
02-07-2011, 05:12 AM
thanks
is sumproduct i can use in macro please advice
where issticky thread "
i need a macro to do the calculattion
mancubus
02-07-2011, 07:39 AM
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
02-07-2011, 07:57 AM
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
02-07-2011, 09:27 AM
string. :banghead:
pls let me know if below code produces any errors.
str1 = """January"""
str2 = """wiring"""
mancubus
02-07-2011, 12:25 PM
sum_result = Application.Evaluate("SUMPRODUCT((year!U2:U500=""January"")*(YEAR!P2:P500=""wiring"")*(year!E2:E500))")
Sheets("SHEET1").Cells("a2").Value = sum_result
mancubus
02-07-2011, 04:08 PM
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
02-07-2011, 11:56 PM
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
02-08-2011, 02:13 AM
trying...
oleg_v
02-08-2011, 02:48 AM
Thanks
i am looking forward for your answer.
mancubus
02-08-2011, 03:07 AM
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
02-08-2011, 04:06 AM
HI
the result gives me "0"
attached file like you requested.
thanks Oleg
mancubus
02-08-2011, 06:38 AM
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
03-07-2011, 12:45 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.