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:
Sort by date Sort by votes
Hello WarPiglet the Do While loop keeps crazing my Excel. I not sure why, but when I modify what you've provided to my requirements, it takes for ever to run through the loop and inevitably requires a forced shut down of Excel.
 
Upvote 0

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
MercuryVBA said:
Hello WarPiglet the Do While loop keeps crazing my Excel. I not sure why, but when I modify what you've provided to my requirements, it takes for ever to run through the loop and inevitably requires a forced shut down of Excel.
Click to expand...

Also WarPiglet - I have a dynamic number of columns with date headers - hence the reason for structuring the code the way that I did in my post #9 where I dynamically increment the date column using "NextCol" ... I can't list the column ranges individually to write the value as you are suggesting in the following lines of code:

Code: 
        Sheets("Summary").Range("D" & d).Value = s         Sheets("Summary").Range("E" & d).Value = e</pre>
 
Upvote 0
This was my first time using dynamic columns. Thanks for helping me learn. Here is the working code.
Code: 
Sub Macro1()
    Sht1 = "Summary"
    Sht2 = "Report"
    
    colNum = 4
    If Sheets(Sht1).Range("D1").Value <> "" Then
        col_i = 4
        col = Chr(col_i + 64)
        Do Until Sheets(Sht1).Range(col & "1").Value = ""
            d = 2
            Do Until Sheets(Sht1).Range("B" & d).Value = ""
                s = 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(col & "1").Value Then
                                s = s + Sheets(Sht2).Range("E" & r).Value
                            End If
                        End If
                    End If
                    r = r + 1
                Loop
                Sheets(Sht1).Range(col & d).Value = s
                d = d + 1
            Loop
            col_i = col_i + 1
            col = Chr(col_i + 64)
        Loop
    End If
End Sub
 
Upvote 0
and actually you can delete this if statement from the code.
Code: 
If Sheets(Sht1).Range("D1").Value <> "" Then



I believe it is already covered in this code anyway.
Code: 
Do Until Sheets(Sht1).Range(col & "1").Value = ""
 
Upvote 0
WarPiglet said:
This was my first time using dynamic columns. Thanks for helping me learn. Here is the working code.
Code: 
Sub Macro1()
    Sht1 = "Summary"
    Sht2 = "Report"
    
    colNum = 4
    If Sheets(Sht1).Range("D1").Value <> "" Then
        col_i = 4
        col = Chr(col_i + 64)
        Do Until Sheets(Sht1).Range(col & "1").Value = ""
            d = 2
            Do Until Sheets(Sht1).Range("B" & d).Value = ""
                s = 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(col & "1").Value Then
                                s = s + Sheets(Sht2).Range("E" & r).Value
                            End If
                        End If
                    End If
                    r = r + 1
                Loop
                Sheets(Sht1).Range(col & d).Value = s
                d = d + 1
            Loop
            col_i = col_i + 1
            col = Chr(col_i + 64)
        Loop
    End If
End Sub
Click to expand...

WarPiglet, Thank you very much for spending so much time to help me solve my problem thus far. I sincerely appreciate it it. However, although the solution you presented in post #14 works on the sample test file that I sent to you, it won't work on my actual. For example, in my actual "Summary" / data file, I have columns that exceed range "Z" and go into cols AA - ZA - if you use chr (col_i + 64) I believe we are limited to "Z" as the last column, then we'll get crazy headers from the ASCII table.

Honestly - I really need a solution to resolve the code I presented in post #9 - Nonetheless, I am glad we both learned something from each other through this positive interaction Thanks again!

P.S. I'll give your solution another try to see if I can mold it into my code.
 
Last edited:
Upvote 0
War Piglet - I've been able to resolve my issue using my code in post #9 - however, looking at your method with the Do While is what gave me the idea to develop it the way that I did to solve my issue - Thank you again for your assistance.

The core of the code is in the If Then Else - I had issues in other areas that were returning incorrect results ... I'm good to go now. Have a good one!
 
Upvote 0
Awesome. I wasn't going to have time to work on your issue for another 2 days so I'm glad you got it working. Those do until loops are awesome.
 
Upvote 0
Turns out I had some time after all. I know you already had a solution. But here is my solution. I tested it. It can go all the way to ZZ.

Code: 
Sub Macro1()
    Sht1 = "Summary"
    Sht2 = "Report"
    
    colNum = 4
    If Sheets(Sht1).Range("D1").Value <> "" Then
        col_i = 4
        col_d = 0
        col = Chr(col_i + 64)
        Do Until Sheets(Sht1).Range(col & "1").Value = ""
            d = 2
            Do Until Sheets(Sht1).Range("B" & d).Value = ""
                s = 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(col & "1").Value Then
                                s = s + Sheets(Sht2).Range("E" & r).Value
                            End If
                        End If
                    End If
                    r = r + 1
                Loop
                Sheets(Sht1).Range(col & d).Value = s
                d = d + 1
            Loop
            col_i = col_i + 1
            If col_i > 26 Then
                col_i = 1
                col_d = col_d + 1
            Else
            End If
            If col_d > 0 Then
                col = Chr(col_d + 64) & _
                Chr(col_i + 64)
            Else
                col = Chr(col_i + 64)
            End If
        Loop
    End If
End Sub
 
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
267
alessiaexcels
A
C
  • Solved
VBA: Using Dictionary or Arrays to sum based on multiple columns?
Replies
7
Views
672
Coyotex3
C
T
  • Question Question
Formula to sum multiple sequences/ranges of values within an array?
Replies
2
Views
250
Fluff
Fluff
C
  • Question Question
Create code that sends emails and creates a log
Replies
1
Views
287
cokeoverpepsi123
C
C
  • Solved
Formula help to calculate RN to Patient ratios based off certain conditions
Replies
3
Views
450
charliemike9285
C

Forum statistics

Threads
1,223,912
Messages
6,175,340
Members
452,638
Latest member
Oluwabukunmi

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