VBA with sumif function while using condition

[gurtejhira]

I have two rows A and B . I like to write a VBA with sumif statement . sum the data in B if the first letter in column A is 4 or 6 or 7.

Below mentioned is the code i tried but somehow its giving me only zero value. Please help me to identify what wrong i m doing.



Sub YardiDASR()
Dim a As Long, b As Long, c As Long
Dim lastrow As Long
Dim mycell As Range
a = 0
b = 0
c = 0
d = 0

lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

For i = 3 To lastrow

If Left(Cells(i, 1), 1) = "4" Then
a = Cells(i, 7) + a
ElseIf Left(Cells(i, 1), 1) = "6" Then
b = Cells(i, 7) + b
ElseIf Left(Cells(i, 1), 1) = "7" Then
c = Cells(i, 7) + c
Else
d = Cells(i, 7) + d
End If
Next i

Range("J2") = a
Range("K2") = b
Range("L2") = c
Range("M2") = d

End Sub

 

[Scott Huish]

Re: How to write vba with sumif function while using condition

Try this:

Sub YardiDASR()
Dim lastrow As Long
lastrow = Range("A" & Rows.Count).End(xlUp).Row
Range("J2").Formula = "=SUMPRODUCT(--(LEFT(A3:A" & lastrow & ")=""4""))"
Range("K2").Formula = "=SUMPRODUCT(--(LEFT(A3:A" & lastrow & ")=""6""))"
Range("L2").Formula = "=SUMPRODUCT(--(LEFT(A3:A" & lastrow & ")=""7""))"
Range("M2").Formula = "=COUNTA(A3:A" & lastrow & ")-SUM(J2:L2)"
Range("J2:M2").Copy
Range("J2:M2").PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub

 

[gurtejhira]

Re: How to write vba with sumif function while using condition

Scott, I m getting count of the row but i m looking for sum of the fields.

 

[Scott Huish]

Re: How to write vba with sumif function while using condition

Sub YardiDASR()
Dim lastrow As Long
lastrow = Range("A" & Rows.Count).End(xlUp).Row
Range("J2").Formula = "=SUMPRODUCT(--(LEFT(A3:A" & lastrow & ")=""4""),a3:a" & lastrow & ")"
Range("K2").Formula = "=SUMPRODUCT(--(LEFT(A3:A" & lastrow & ")=""6""),a3:a" & lastrow & ")"
Range("L2").Formula = "=SUMPRODUCT(--(LEFT(A3:A" & lastrow & ")=""7""),a3:a" & lastrow & ")"
Range("M2").Formula = "=SUM(A3:A" & lastrow & ")-SUM(J2:L2)"
Range("J2:M2").Copy
Range("J2:M2").PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub

 

[gurtejhira]

Re: How to write vba with sumif function while using condition

Still I m getting 0

 

[Scott Huish]

Re: How to write vba with sumif function while using condition

Can you post some sample data?

This is what I got after running my code

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2										4400	0	7000	5000
3	400
4	4000
5	5000
6	7000
Sheet1

 

[gurtejhira]

Re: How to write vba with sumif function while using condition

A b
4010-0000 12500
4040-0000 500
6310-0000 21000
6415-0000 900

a = 13000
b =21900

 

[Scott Huish]

Re: How to write vba with sumif function while using condition

Sub YardiDASR()
Dim lastrow As Long
lastrow = Range("A" & Rows.Count).End(xlUp).Row
Range("J2").Formula = "=SUMPRODUCT(--(LEFT(A3:A" & lastrow & ")=""4""),B3:B" & lastrow & ")"
Range("K2").Formula = "=SUMPRODUCT(--(LEFT(A3:A" & lastrow & ")=""6""),B3:B" & lastrow & ")"
Range("L2").Formula = "=SUMPRODUCT(--(LEFT(A3:A" & lastrow & ")=""7""),B3:B" & lastrow & ")"
Range("M2").Formula = "=SUM(B3:B" & lastrow & ")-SUM(J2:L2)"
Range("J2:M2").Copy
Range("J2:M2").PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub

 

[gurtejhira]

Re: How to write vba with sumif function while using condition

Thank you Scott.. Its working fine.