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

M

MercuryVBA

Board Regular
Joined
Nov 12, 2013
Messages
56
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
[TABLE="width: 370"]
<colgroup><col><col span="2"><col><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 370"]
<colgroup><col><col span="2"><col><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD] C
[/TD]
[TD] D
[/TD]
[TD] E
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Serial #[/TD]
[TD]Classificaiton[/TD]
[TD] 8/8/2014
[/TD]
[TD] 8/15/2014
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]John[/TD]
[TD]12345
[/TD]
[TD] PT
[/TD]
[TD] 10
[/TD]
[TD] 7
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Jack[/TD]
[TD]23456[/TD]
[TD] FT
[/TD]
[TD] 12
[/TD]
[TD] 8
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Christie[/TD]
[TD]34567[/TD]
[TD] PT
[/TD]
[TD] 1
[/TD]
[TD] 7
[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Charlie[/TD]
[TD]45678[/TD]
[TD] FT
[/TD]
[TD] 3
[/TD]
[TD] 8
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
"Report" Sheet

[TABLE="width: 394"]
<colgroup><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Serial #[/TD]
[TD]Classificaiton[/TD]
[TD]Date[/TD]
[TD]Hrs[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]John[/TD]
[TD]12345[/TD]
[TD]PT[/TD]
[TD]8/8/2014[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Jack[/TD]
[TD]23456[/TD]
[TD]FT[/TD]
[TD]8/8/2014[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Christie[/TD]
[TD]34567[/TD]
[TD]PT[/TD]
[TD]8/8/2014[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Charlie[/TD]
[TD]45678[/TD]
[TD]FT[/TD]
[TD]8/8/2014[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]John[/TD]
[TD]12345[/TD]
[TD]PT[/TD]
[TD]8/8/2014[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Jack[/TD]
[TD]23456[/TD]
[TD]FT[/TD]
[TD]8/8/2014[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Christie[/TD]
[TD]34567[/TD]
[TD]PT[/TD]
[TD]8/15/2014[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Charlie[/TD]
[TD]45678[/TD]
[TD]FT[/TD]
[TD]8/15/2014[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]John[/TD]
[TD]12345[/TD]
[TD]PT[/TD]
[TD]8/15/2014[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Jack[/TD]
[TD]23456[/TD]
[TD]FT[/TD]
[TD]8/15/2014[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Christie[/TD]
[TD]34567[/TD]
[TD]PT[/TD]
[TD]8/15/2014[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Charlie[/TD]
[TD]45678[/TD]
[TD]FT[/TD]
[TD]8/15/2014[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
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?
 
Upvote 0
WarPiglet said:
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?
Click to expand...

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!
 
Upvote 0
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.
Code: 
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
WarPiglet said:
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.
Click to expand...

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!
 
Upvote 0
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:

[TABLE="width: 404"]
<tbody>[TR]
[TD]"Summary"-Sheet[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD]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.

[TABLE="width: 404"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Serial #[/TD]
[TD]Class[/TD]
[TD]8/8/2014[/TD]
[TD]8/15/2014[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]John[/TD]
[TD]12345[/TD]
[TD]PT[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Jack[/TD]
[TD]23456[/TD]
[TD]FT[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Christie[/TD]
[TD]34567[/TD]
[TD]PT[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Charlie[/TD]
[TD]45678[/TD]
[TD]FT[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]"Reports"-Sheet
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][TABLE="width: 428"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Serial #[/TD]
[TD]Class[/TD]
[TD]Date[/TD]
[TD]Hrs[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]John[/TD]
[TD]12345[/TD]
[TD]PT[/TD]
[TD]8/8/2014[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Jack[/TD]
[TD]23456[/TD]
[TD]FT[/TD]
[TD]8/8/2014[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Christie[/TD]
[TD]34567[/TD]
[TD]PT[/TD]
[TD]8/8/2014[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Charlie[/TD]
[TD]45678[/TD]
[TD]FT[/TD]
[TD]8/8/2014[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]John[/TD]
[TD]12345[/TD]
[TD]PT[/TD]
[TD]8/8/2014[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Jack[/TD]
[TD]23456[/TD]
[TD]FT[/TD]
[TD]8/8/2014[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Christie[/TD]
[TD]34567[/TD]
[TD]PT[/TD]
[TD]8/15/2014[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Charlie[/TD]
[TD]45678[/TD]
[TD]FT[/TD]
[TD]8/15/2014[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]John[/TD]
[TD]12345[/TD]
[TD]PT[/TD]
[TD]8/15/2014[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Jack[/TD]
[TD]23456[/TD]
[TD]FT[/TD]
[TD]8/15/2014[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Christie[/TD]
[TD]34567[/TD]
[TD]PT[/TD]
[TD]8/15/2014[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Charlie[/TD]
[TD]45678[/TD]
[TD]FT[/TD]
[TD]8/15/2014[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Code: 
            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
 
Last edited:
Upvote 0
Let me know if this worked for you.
Code: 
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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Sum hours by date range by employee ID
Replies
3
Views
263
alessiaexcels
A
C
  • Solved
VBA: Using Dictionary or Arrays to sum based on multiple columns?
Replies
7
Views
639
Coyotex3
C
T
  • Question Question
Formula to sum multiple sequences/ranges of values within an array?
Replies
2
Views
247
Fluff
Fluff
C
  • Question Question
Create code that sends emails and creates a log
Replies
1
Views
266
cokeoverpepsi123
C
C
  • Solved
Formula help to calculate RN to Patient ratios based off certain conditions
Replies
3
Views
408
charliemike9285
C

Forum statistics

Threads
1,223,239
Messages
6,170,947
Members
452,368
Latest member
jayp2104

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top