Solved: Multiple values using SUMPRODUCT

[justdriving]

Hi

I want to calculate Employee - wise Counts of Col E Data on a given date. I have an idea that SUMPRODUCT can do that. But, how can I use in VBA?

[Bob Phillips]

Use

=SUMPRODUCT(--($A$2:$A$50=$I3),--($E$2:$E$50=J$2))

[justdriving]

Hi,

I request your help to

(1) Include third condition of Date given in Row 1
(2) exclude this R1C1 notation: -


Sub test()
Range("J3").Select
ActiveCell.FormulaR1C1 = "=SUMPRODUCT(--(R2C1:R50C1=RC9),--(R2C5:R50C5=R2C))"
Range("J3").Select
Selection.AutoFill Destination:=Range("J3:J11"), Type:=xlFillDefault
Range("J3:J11").Select
Selection.AutoFill Destination:=Range("J3:X11"), Type:=xlFillDefault
Range("J3:X11").Select
End Sub

[mancubus]

Range("J3:X11").Formula = "=SUMPRODUCT(--($A$2:$A$50=$I3),--($D$2:$D$50=J$1),--($E$2:$E$50=J$2))"

[justdriving]

This worked, but users don't want formula in cells. I have a program which can measure End of row and End of Column. I think I missed 3rd loop to include one more condition of DATE. Can you please help, Bob?


Sub Macro1()

Dim ir, jc As Integer

With Worksheets(1)

For jc = 10 To .Range("j2").End(xlToRight).Column

For ir = 2 To .Range("i3").End(xlDown).Row

.Cells(ir, jc).Formula = "=SUMPRODUCT(--(A2:A1002=" & .Cells(ir, "i").Address & "),--(E2:E1002=" & .Cells(2, jc).Address & "))"
.Cells(ir, jc).Value = .Cells(ir, jc).Value

Next ir

Next jc

End With

End Sub


Bob is "Best of best".

[Bob Phillips]

Sub Macro1()
Const FORMULA_COUNT As String = _
"=SUMPRODUCT(--(A2:A1002=$I3),--(E2:E1002=<col>$2),--(F2:F1002=<date>))"
Const DATE_TEST As String = "01-02-2011"
Dim lastrow As Long
Dim ir As Long, jc As Long

With Worksheets(1)

lastrow = .Range("I3").End(xlDown).Row

For jc = 10 To .Range("J2").End(xlToRight).Column

With .Cells(3, jc).Resize(lastrow - 2)

.Formula = Replace(Replace(FORMULA_COUNT, _
"<date>", CLng(CDate(DATE_TEST))), _
"<col>", Mid$(.Cells(2, 1).Address(True, False), 1, 1))
.Value = .Value
End With
Next jc
End With
End Sub

[justdriving]

I am requesting to review this code. Its Output marked every cell as 0. It was not able to count.

[Bob Phillips]

Post the workbook.

[justdriving]

It will be best if we can calculate End Row of Col A, E and F and put macro operations till last row of data to speed up program. Work attached here.

[Bob Phillips]

Sub Macro1()
Const FORMULA_COUNT As String = _
"=SUMPRODUCT(--($A$2:$A$1002=$I3),--($E$2:$E$1002=<col>$2),--($D$2:$D$1002=<col>$1))"
Dim lastrow As Long
Dim lastcol As Long
Dim ir As Long, jc As Long

With Worksheets(1)

lastrow = .Range("I3").End(xlDown).Row

lastcol = .Range("J2").End(xlToRight).Column

With .Range("J3").Resize(lastrow - 2, lastcol - 9)

.Formula = Replace(FORMULA_COUNT, "<col>", Mid$(.Cells(2, 1).Address(True, False), 1, 1))
.Value = .Value
End With
End With
End Sub

[justdriving]

This was amazing program which used following:-

Const Statement
SUMPRODUCT
LastRow and LastColumn
MID (http://www.techonthenet.com/excel/formulas/mid.php) Function
Replace function
.Address (http://www.excel-spreadsheet.com/vba/rangeselectionobjects.htm)(True, False)
Moreover, this program appeared to me as "very professional". I am really thankful to you, Bob.