Help Converting Multi Condition Sum(IF(...)) Array Formula Into VBA Syntax

[MercuryVBA]

Hello,

On the attached data workbook there are two sheets. "Summary" and "Report".

Starting with row 2, column D I've created a multi criteria sum(if()) function that does the following:

On Cell D2 I sum up all the hours on the "Report" sheet where there is a match for B2, C2 and D1 from the "Summary" sheet.

Cell D2 Formula = {=SUM(IF((Report!$B$2:$B$13=Summary!$B2)*(Report!$C$2:$C$13=$C2)*(Report!$D$2:$D$13=D$1),Report!$E$2:$E$13,0))}

I would like to convert this formula to VBA for my macro to perform the calculation without inserting the formula into the cells. The macro should just return the results to the cells.

I tried this in VBA using .Application.WorksheetFunction.SumProduct() - but could not get it to work.

Any help would be much appreciated. Thank you!

"Summary" Sheet

A B C D E 1 Name Serial # Classificaiton 8/8/2014 8/15/2014 2 John 12345 PT 10 7 3 Jack 23456 FT 12 8 4 Christie 34567 PT 1 7 5 Charlie 45678 FT 3 8 <colgroup><col><col span="2"><col><col span="2"></colgroup><tbody> </tbody>

<colgroup><col><col span="2"><col><col span="2"></colgroup><tbody>
</tbody>

"Report" Sheet

	A	B	C	D	E
1	Name	Serial #	Classificaiton	Date	Hrs
2	John	12345	PT	8/8/2014	4
3	Jack	23456	FT	8/8/2014	4
4	Christie	34567	PT	8/8/2014	1
5	Charlie	45678	FT	8/8/2014	3
6	John	12345	PT	8/8/2014	6
7	Jack	23456	FT	8/8/2014	8
8	Christie	34567	PT	8/15/2014	4
9	Charlie	45678	FT	8/15/2014	2
10	John	12345	PT	8/15/2014	7
11	Jack	23456	FT	8/15/2014	8
12	Christie	34567	PT	8/15/2014	3
13	Charlie	45678	FT	8/15/2014	6

<colgroup><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>

 

[WarPigl3t]

What your asking is fairly simple. I just need to know a bit more information first.
Is the macro going to be loaded to a formula?
Is the macro going to be on change event?
Is the macro going to be a function?
How many rows should I account for?

 

[MercuryVBA]

What your asking is fairly simple. I just need to know a bit more information first.
Is the macro going to be loaded to a formula?
Is the macro going to be on change event?
Is the macro going to be a function?
How many rows should I account for?

Hello Thank you - No The Macro won't be loaded to a formula ...

I just want the Macro to calculate the values and write it to the cells.

Is the macro going to be on change event? - I'm sorry, I don't understand this question.

Is the macro going to be a function? - No

How many rows should I account for? - Rows will be dynamic based on number of rows in the "Report" sheet.

Thank you!

 

[WarPigl3t]

There is some stuff wrong with my logic as I do not fully understand what you want the output to be. Unfortunately I have to go to work now so I can't help you anymore. I created this VBA code. It is self explanatory. You should be able to figure out how to make it do exactly what you want using my code. Just modify it to do what you want. I got you most of the way there.

Sub Macro1()
    e = 2
    Do Until Sheets("Summary").Range("B" & e).Value = ""
        s = 0
        r = 2
        Do Until Sheets("Report").Range("B" & r).Value = ""
            If Sheets("Report").Range("B" & r).Value = Sheets("Summary").Range("B" & e).Value Then
                If Sheets("Report").Range("C" & r).Value = Sheets("Summary").Range("C" & e).Value Then
                    If Sheets("Report").Range("D" & r).Value = Sheets("Summary").Range("D1").Value Then
                        s = s + 1
                    End If
                End If
            End If
            r = r + 1
        Loop
        Sheets("Summary").Range("E" & e).Value = s
        e = e + 1
    Loop
End Sub

 

[WarPigl3t]

The code works perfectly. Disregard my last message about it not working. It was a user error on my part. Use the code I already gave you

 

[WarPigl3t]

I asked you earlier if you were going to use a change event. What I meant by that was if you wanted the code to run every time a value in a cell changed. Or are you just going to put it into a button. It doesn't matter anymore though. I already gave you working code. You can easily figure out how to implement an on change event or load it to a button.

 

[WarPigl3t]

Sorry I didnt give you code that you needed exactly I just realized. Delete where it says "s=s+1" and replace it with "s=s+Sheets("Report").Range("E" & r).Value" without the quotes.

 

[MercuryVBA]

Sorry I didnt give you code that you needed exactly I just realized. Delete where it says "s=s+1" and replace it with "s=s+Sheets("Report").Range("E" & r).Value" without the quotes.

Thank you so very much for your help. I've all out of gas tonight - but I will try this out tomorrow. Thank you again!

 

[MercuryVBA]

Hello,

Thank you again for your help thus far - unfortunately I couldn't solve my problem with the code you provided a few days ago - It did however point me to develop the following code which better suits my objective. Nonetheless, I can't get my code to work - it is not SUMMING anything up ... Would you kindly review my code and see what is wrong. Again, my main objective is essentially to emulate a Sum(IF()) array formula but within VBA without loading the formula to the worksheet cells.

Data:

"Summary"-Sheet
	The goal is to populate D2 through E5 with the VBA code. For e.g. D2 will sum up all hours on the "Reports" sheet that matches the Serial#, Class and The Header Date. A B C D E 1 Name Serial # Class 8/8/2014 8/15/2014 2 John 12345 PT 3 Jack 23456 FT 4 Christie 34567 PT 5 Charlie 45678 FT <tbody> </tbody>
"Reports"-Sheet
	A B C D E 1 Name Serial # Class Date Hrs 2 John 12345 PT 8/8/2014 4 3 Jack 23456 FT 8/8/2014 4 4 Christie 34567 PT 8/8/2014 1 5 Charlie 45678 FT 8/8/2014 3 6 John 12345 PT 8/8/2014 6 7 Jack 23456 FT 8/8/2014 8 8 Christie 34567 PT 8/15/2014 4 9 Charlie 45678 FT 8/15/2014 2 10 John 12345 PT 8/15/2014 7 11 Jack 23456 FT 8/15/2014 8 12 Christie 34567 PT 8/15/2014 3 13 Charlie 45678 FT 8/15/2014 6 <tbody> </tbody>

<tbody>
</tbody>

            Set WSDM = Worksheets("Summary")
            Set WSReports = Worksheets("Reports")

            FinalRowSummary = WSDM.Cells(Rows.Count, 2).End(xlUp).Row
            FinalRowReports = WSReports.Cells(Rows.Count, 1).End(xlUp).Row
            
            LastColSummary = WSDM.Cells(1, Columns.Count).End(xltoLeft).Column

            For k = 2 To FinalRowSummary
            For NextCol = 4 to LastColSummary

            CompareDate = WSDM.Cells(1, NextCol)  
            EmplID = WSDM.Cells(k, 2).Value
            PayType = WSDM.Cells(k, 3).Value
                        
            HrsWrkd = 0            
            
            With Application.WorksheetFunction
            If EmplID <> "" And .CountIf(WSReports.Range("B2:B" & FinalRowWSReports), EmplID) Then  
                     'The above line just checks to see if the emplID is present in the Reports Sheet.
                                                                                                                                         
                For i = 2 To FinalRowWSReports
                    
                    If WSReports.Range("B" & i).Value = EmplID Then                                                    
                            If WSReports.Range("C" & i).Value = PayType Then
                                    If WSReports.Range("D" & i).Value = CompareDate Then
                                        HrsWrkd = HrsWrkd + WSReports.Range("E" & i).Value
                                    End If
                             End If                           
                    End If
                Next i
            End If
            End With
            
            WSDM.Cells(k, NextCol).Value = HrsWrkd

            Next k
            Next NextCol

 

[WarPigl3t]

Let me know if this worked for you.

Sub Macro1()
    Sht1 = "Summary"
    Sht2 = "Report"
    d = 2
    Do Until Sheets(Sht1).Range("B" & d).Value = ""
        s = 0
        e = 0
        r = 2
        Do Until Sheets(Sht2).Range("B" & r).Value = ""
            If Sheets(Sht2).Range("B" & r).Value = Sheets(Sht1).Range("B" & d).Value Then
                If Sheets(Sht2).Range("C" & r).Value = Sheets(Sht1).Range("C" & d).Value Then
                    If Sheets(Sht2).Range("D" & r).Value = Sheets(Sht1).Range("D1").Value Then
                        s = s + Sheets(Sht2).Range("E" & r).Value
                    End If
                    If Sheets(Sht2).Range("D" & r).Value = Sheets(Sht1).Range("E1").Value Then
                        e = e + Sheets(Sht2).Range("E" & r).Value
                    End If
                End If
            End If
            r = r + 1
        Loop
        Sheets(Sht1).Range("D" & d).Value = s
        Sheets(Sht1).Range("E" & d).Value = e
        d = d + 1
    Loop
End Sub