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!

 

[MercuryVBA]

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.

M. seeing this through will be a learning experience for me - therefore, I'll provide the values for one current static instance (the range may expand dynamically hence the reason for dynamic definition)

Range(F3:AN3)
Range(F2:AN17)

 

[Marcelo Branco]

M. seeing this through will be a learning experience for me - therefore, I'll provide the values for one current static instance (the range may expand dynamically hence the reason for dynamic definition)

Range(F3:AN3)
Range(F2:AN17)

We have a problem

Take a look at the Help file and you can see that MATCH works only with one dimensional arrays (vectors in technical terms).
So probably you need to correct the Range(F2:AN17) to Range(F2:AN2) to be consistent with INDEX(F3:AN3,...

M.

 

[MercuryVBA]

We have a problem

Take a look at the Help file and you can see that MATCH works only with one dimensional arrays (vectors in technical terms).
So probably you need to correct the Range(F2:AN17) to Range(F2:AN2) to be consistent with INDEX(F3:AN3,...

M.

M. You are right - the multi-dimension was throwing it off. Thank you again for everything!

 

[Marcelo Branco]

M. You are right - the multi-dimension was throwing it off. Thank you again for everything!

Great! Glad for help.

M.

 

[MercuryVBA]

Hello M. Hope you are doing well. If you have some time, would you kindly take a look at my new post below? Thank you!

http://www.mrexcel.com/forum/excel-...olumn-letter-use-dynamic-range-reference.html