VBA Sum( IF(Statement, if True, If False ) ) array function Syntax

[MercuryVBA]

Hello,

Would anyone be able to have me resolve the correct way to write this SUM ( IF() ) nested array function in VBA?

With Application.WorksheetFunction

.Sum(.IF(Sht2.Range("O2:O" & FinalRowSht2) = EmplID, Sht2.Range("AD2:AD" & FinalRowSht2), 0))

End With

I've defined "EmplID" and "FinalRowSht2" correctly earlier in the code - I'm excluding that definition here just to simplify

Many Thanks!

 

[Marcelo Branco]

Why are you using SUM(IF...)?
It seems that you need a simple VLOOKUP, i.e., given the Serial# get the Hrs from Sheet2 column AD.

In C2 of Sheet1
=VLOOKUP(B2,Sheet2!O:AD,16,0)

Could you clarify why you are not using a formula like this?

M.

 

[MercuryVBA]

M. I'm using Sum IF because ultimately I have multiple criteria to match from Sheet 1 to Sheet 2 - VLookup just won't serve my purpose - Also I can easily write functions in the cells using nested SUM IFs but my objective is to do the calculations dynamically and programetically ... I totally simplified the sample data set I provided to try to avoid confusion and get a base code for VBA SUM IF that I could work off of.

The original code you provided is promising, I'm just not getting why VBA does not pick up the correct range from Sheet 2 ...

 

[Marcelo Branco]

I totally simplified the sample data set I provided to try to avoid confusion and get a base code for VBA SUM IF that I could work off of.

Could you, please, provide a simplified data sample but that is similar to your real case.
Otherwise i'm afraid i cannot understand you problem.

M.

 

[MercuryVBA]

Could you, please, provide a simplified data sample but that is similar to your real case.
Otherwise i'm afraid i cannot understand you problem.

M.

"Sheet 1"

	A	B	C	D	E	F ....
1	Name	Serial #	Class	8/8/2014	8/15/2014	Next Date….
2	John	12345	PT
3	Jack	23456	FT
4	Christie	34567	PT
5	Charlie	45678	FT

<tbody>
</tbody>

"Sheet 2"

	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>

Ok - so I have the above two sheets ... If I just wanted to plug in a formula to accomplish my objective, I would simply enter the following in cell D2
then just fill the empty cells with the formula:

=SUM(IF(('Sheet 2'!$B$2:$B$13='Sheet 1'!$B2)*('Sheet 2'!$C$2:$C$13='Sheet 1'!$C2)*('Sheet 2'!$D$2:$D$13='Sheet 1'!D$1),'Sheet 2'!$E$2:$E$13))

HOWEVER, I don't want to fill in the cells with formulas. I want my macro to do the calculation in the background and write the result to the empty cells in Sheet1.

So far with your help earlier I've come up with this, but it only returns 0

Dim Sht1 As Worksheet
Dim Sht2 As Worksheet
Set Sht2 = Worksheets("Sheet 1")
Set Sht2 = Worksheets("Sheet 2")

FinalRowSht1 = Sht1.Cells(Rows.Count, 1).End(xlUp).Row
FinalColSht1 = Sht1.Cells(1, Columns.Count).End(xltoLeft).Row

FinalRowSht2 = Sht2.Cells(Rows.Count, 1).End(xlUp).row

For k = 2 to FinalRowSht1
For NextCol = 4 to FinalColSht1

 Sht1.Cells(k, NextCol).Value = Evaluate("=SUM(IF(" & Sht2.Range("B2:B" & FinalRowWSSubALD).Address & "=""" & EmplID & """," & _
               ' Sht2.Range("D:DD" & FinalRowSht2).Address & "))")

               'I'm missing several of the conditional criteria in the IF bracket and I need help with the syntax...
                  
Next NextCol
Next k

 

[Marcelo Branco]

See if this is ok - it worked with your last data sample (post #14)
Assumes the sheet names are Sheet1 and Sheet2, not Sheet 1 and Sheet 2

Sub aTest()
    Dim Sht1 As Worksheet, Sht2 As Worksheet
    Dim FinalRowSht1 As Long, FinalRowSht2 As Long, FinalColSht1 As Long
    Dim k As Long, NextCol As Long
    
    Set Sht1 = Worksheets("Sheet1")
    Set Sht2 = Worksheets("Sheet2")
    
    FinalRowSht1 = Sht1.Cells(Rows.Count, 1).End(xlUp).Row
    FinalColSht1 = Sht1.Cells(1, Columns.Count).End(xlToLeft).Column
    
    With Sht2
        FinalRowSht2 = .Cells(.Rows.Count, 1).End(xlUp).Row
    
        For k = 2 To FinalRowSht1
            For NextCol = 4 To FinalColSht1
    
            Sht1.Cells(k, NextCol).Value = _
                Evaluate("=SUM(IF(" & .Range("B2:B" & FinalRowSht2).Address(, , , True) & "=" & Sht1.Range("B" & k).Address & "," & _
                   "IF(" & .Range("C2:C" & FinalRowSht2).Address(, , , True) & "=" & Sht1.Range("C" & k).Address & "," & _
                   "IF(" & .Range("D2:D" & FinalRowSht2).Address(, , , True) & "=" & Sht1.Cells(1, NextCol).Address & "," & _
                   .Range("E2:E" & FinalRowSht2).Address(, , , True) & "))))")
                   
            Next NextCol
        Next k
        
    End With
End Sub

Hope it also works for you.

M.

 

[MercuryVBA]

M. You are the man!!! That did the trick -thank you again for bearing with me on this, really appreciate it!

See if this is ok - it worked with your last data sample (post #14)
Assumes the sheet names are Sheet1 and Sheet2, not Sheet 1 and Sheet 2

Sub aTest()
    Dim Sht1 As Worksheet, Sht2 As Worksheet
    Dim FinalRowSht1 As Long, FinalRowSht2 As Long, FinalColSht1 As Long
    Dim k As Long, NextCol As Long
    
    Set Sht1 = Worksheets("Sheet1")
    Set Sht2 = Worksheets("Sheet2")
    
    FinalRowSht1 = Sht1.Cells(Rows.Count, 1).End(xlUp).Row
    FinalColSht1 = Sht1.Cells(1, Columns.Count).End(xlToLeft).Column
    
    With Sht2
        FinalRowSht2 = .Cells(.Rows.Count, 1).End(xlUp).Row
    
        For k = 2 To FinalRowSht1
            For NextCol = 4 To FinalColSht1
    
            Sht1.Cells(k, NextCol).Value = _
                Evaluate("=SUM(IF(" & .Range("B2:B" & FinalRowSht2).Address(, , , True) & "=" & Sht1.Range("B" & k).Address & "," & _
                   "IF(" & .Range("C2:C" & FinalRowSht2).Address(, , , True) & "=" & Sht1.Range("C" & k).Address & "," & _
                   "IF(" & .Range("D2:D" & FinalRowSht2).Address(, , , True) & "=" & Sht1.Cells(1, NextCol).Address & "," & _
                   .Range("E2:E" & FinalRowSht2).Address(, , , True) & "))))")
                   
            Next NextCol
        Next k
        
    End With
End Sub

Hope it also works for you.

M.

 

[Marcelo Branco]

M. You are the man!!! That did the trick -thank you again for bearing with me on this, really appreciate it!

You are very welcome. Glad for helping

M.

 

[MercuryVBA]

Hello again M.

So sorry to keep bother you today - but would you kindly check my syntax in the following function? : If you require more clarification please ask and I will clarify in detail - this is for a new segment of my code that I'm stuck on:

VacHrs = .Index(WSVP.Range("F3:" & WSVP.Cells(FinalRowWSVP, FinalColWSVP).Address), _
                                                .Match(CompareDate, WSVP.Range("F2:" & WSVP.Cells(2, FinalColWSVP).Address), 0))

 

[Marcelo Branco]

Check syntax is not a very amusing task

But let me try...

Supposing it's inside

With WorksheetFunction
....
End With

As far as I know the structure of your formula seems correct.

In fact it depends on the values of
WSVP.Range("F3:" & WSVP.Cells(FinalRowWSVP, FinalColWSVP).Address
and
WSVP.Range("F2:" & WSVP.Cells(2, FinalColWSVP).Address

Could you tell us these values?

M.

 

[MercuryVBA]

M. nevermind - I solved my problem using another method with multiple FOR loops nested with IF statements. Thanks again!