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

M

MercuryVBA

Board Regular
Joined
Nov 12, 2013
Messages
56
Hello,

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

Code: 
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!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
Evaluate worked for me. See the example below.

Code: 
Sub aTest()
    Dim Total As Double, Sht2 As Worksheet, EmplID As String, FinalRowSht2 As Long
    
    Set Sht2 = Sheets("Sheet1") '<--Adjust sheet name
    EmplID = "ID4" '<-- Adjust ID of interest
    
    With Sht2
        FinalRowSht2 = .Cells(.Rows.Count, "O").End(xlUp).Row
    
        Total = Evaluate("=SUM(IF(" & .Range("O2:O" & FinalRowSht2).Address & "=""" & EmplID & """," & _
                .Range("AD2:AD" & FinalRowSht2).Address & "))")
    
        MsgBox Total
    End With
    
End Sub

Hope this helps

M.
 
Upvote 0
Marcelo Branco said:
Evaluate worked for me. See the example below.

Code: 
Sub aTest()
    Dim Total As Double, Sht2 As Worksheet, EmplID As String, FinalRowSht2 As Long
    
    Set Sht2 = Sheets("Sheet1") '<--Adjust sheet name
    EmplID = "ID4" '<-- Adjust ID of interest
    
    With Sht2
        FinalRowSht2 = .Cells(.Rows.Count, "O").End(xlUp).Row
    
        Total = Evaluate("=SUM(IF(" & .Range("O2:O" & FinalRowSht2).Address & "=""" & EmplID & """," & _
                .Range("AD2:AD" & FinalRowSht2).Address & "))")
    
        MsgBox Total
    End With
    
End Sub

Hope this helps

M.
Click to expand...

Hello Marcelo,

Thank you very much - this is almost the solution I'm looking for but it's not quite getting me there - I believe the solution you provided assumes the Sum Range is in the same sheet as where the function is evaluating.

I actually have two sheets. EmplID is on Sheet 1 Column 1. and I want to Match EmplID on Sheet 2 and return hrs for that EmplID on Sheet 2 on Sheet 1 Column 2.

Here is what I have, but in debug mode it shows that the range value is being read as just "O2:OXXXX" instead of 'Sheet2'!O2:OXXXX - and I think that is what is preventing me from getting my desired output. Can you help me resolve this? Many thanks!

Code: 
Sheet1.Cells(k, NextCol).Value = Evaluate("=SUM(IF(" & Sheet2.Range("O2:O" & FinalRowSheet2).Address & "=""" & EmplID & """," & _
                WSSheet2.Range("AD2:AD" & FinalRowSheet2).Address & "))")
 
Upvote 0
I'm confused :confused:

You are saying that Column O2:OXXX is in Sheet2
What about column AD2:ADxxxx? How did you set up WSSheet2?

M.
 
Upvote 0
MercuryVBA said:
Hi M,

Yes columnAD2:ADXXX is also in sheet 2.

Dim WSSHeet2 As Worksheet
Set WSSheet2 = Worksheets("Sheet2")
Click to expand...

Ok
See if this is ok

Code: 
Sub bTest()
    Dim Total As Double, WSSheet2 As Worksheet, EmplID As String, FinalRowSht2 As Long
    Dim k As Long, NextCol As Long
    
    Set WSSheet2 =Sheets("Sheet2") '<--Adjust sheet name
    EmplID = "ID4" '<-- Adjust ID of interest
    
    'Just for testing purpose
    k = 1
    NextCol = 1
    
    With WSSheet2
[B][COLOR=#0000ff]        .Activate[/COLOR][/B]
        
        FinalRowSht2 = .Cells(.Rows.Count, "O").End(xlUp).Row
    
        Sheet1.Cells(k, NextCol) = Evaluate("=SUM(IF(" & .Range("O2:O" & FinalRowSht2).Address & "=""" & EmplID & """," & _
                .Range("AD2:AD" & FinalRowSht2).Address & "))")
        
    End With
    
End Sub

M.
 
Last edited:
Upvote 0
Marcelo Branco said:
Ok
See if this is ok

Code: 
Sub bTest()
    Dim Total As Double, Sht2 As Worksheet, EmplID As String, FinalRowSht2 As Long
    Dim k As Long, NextCol As Long
    
    Set WSSheet2 =Sheets("Sheet2") '<--Adjust sheet name
    EmplID = "ID4" '<-- Adjust ID of interest
    
    'Just for testing purpose
    k = 1
    NextCol = 1
    
    With WSSheet2
[B][COLOR=#0000ff]        .Activate[/COLOR][/B]
        
        FinalRowSht2 = .Cells(.Rows.Count, "O").End(xlUp).Row
    
        Sheet1.Cells(k, NextCol) = Evaluate("=SUM(IF(" & .Range("O2:O" & FinalRowSht2).Address & "=""" & EmplID & """," & _
                .Range("AD2:AD" & FinalRowSht2).Address & "))")
        
    End With
    
End Sub

M.
Click to expand...

:( Unfortunately that still did not work ...
 
Upvote 0
The code worked for me.

My test-data

Sheet2


O​
P​
Q​
R​
S​
T​
U​
V​
W​
X​
Y​
Z​
AA​
AB​
AC​
AD​
1​
Id​
Values​
2​
Id4​
10​
3​
Id2​
20​
4​
Id3​
30​
5​
Id4​
40​
6​
Id5​
50​
7​
Id6​
60​
8​
Id7​
70​
9​
Id8​
80​
10​
Id9​
90​
11​
Id10​
100​

<tbody>
</tbody>

The code returned 50 (sum of rows 2 and 5)

M.
 
Upvote 0
Marcelo Branco said:
The code worked for me.

My test-data

Sheet2


O​
P​
Q​
R​
S​
T​
U​
V​
W​
X​
Y​
Z​
AA​
AB​
AC​
AD​
1​
Id​
Values​
2​
Id4​
10​
3​
Id2​
20​
4​
Id3​
30​
5​
Id4​
40​
6​
Id5​
50​
7​
Id6​
60​
8​
Id7​
70​
9​
Id8​
80​
10​
Id9​
90​
11​
Id10​
100​

<tbody>
</tbody>

The code returned 50 (sum of rows 2 and 5)

M.
Click to expand...

M. By not working I mean wrong result - the function just returns "0" .... Here is my setup:

Sheet 1
A
B
C
1
Name
Serial #
Hrs
2
John12345Macro Return Hrs From Sheet2 Here
3
Jack23456Macro Return Hrs From Sheet2 Here
4
Christie34567Macro Return Hrs From Sheet2 Here
5
Charlie
45678Macro Return Hrs From Sheet2 Here
6
Timmy2222Macro Return Hrs From Sheet2 Here

<tbody>
</tbody>

Sheet 2
A
O

AD
1
Name
Serial #Hrs
2
John12345
5
3
Jack2345610
4
Christie3456716
5
Charlie
4567820
6
Timmy222230

<tbody>
</tbody>
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Writing many arrays to spreadsheet using .Offset and .Resize syntax
Replies
7
Views
460
Alex Blakenburg
A
S
  • Question
Pivot VBA issue.
Replies
0
Views
293
siddo
S
Agnarr
  • Question
VBA problem with creating new sheets
Replies
0
Views
338
Agnarr
Agnarr
S
  • Solved
VBA: All combinations that sum between two numbers - too much processing blows up
Replies
4
Views
1K
solutelan
S
S
  • Question
VBA Custom function to replicate INDEX MATCH MATCH with predefined fixed arrays
Replies
3
Views
1K
DanteAmor
DanteAmor

Forum statistics

Threads
1,214,643
Messages
6,120,702
Members
448,980
Latest member
CarlosWin

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