[SOLVED] Using OR statement in FormulaArray

[hoffey]

I seem to be stuck trying to use multiple variables in an array statement.

=SUM(IF(Hours!$A$2:$A$13189=HR!$A5,IF(OR(Hours!$B$2:$B$13189="ENG",Hours!$B$2:$B$13189="SNRENG"),Hours!$D$2:$D$13189,),))

Right now it sums everything if the first expression passes, so if the Hours column A matches HR!$A5 then it will sum all that match, disregarding the OR statement and additional matches.

Note I'm trying to use an OR statement because some of the other expressions I am trying to do are too long (above 255 char). Any advice on how to get this to work? I want to check Column A on HR sheet against A5, and then see if the B column makes additional matches to only sum up those.


EDIT: This formula below works, but once I start adding more 'OR' statements it becomes too long and reaches the char limit:

=SUM(IF(Hours!$A$2:$A$13189=HR!$A5,IF(Hours!$B$2:$B$13189="ENG",Hours!$D$2:$D$13189,),),IF(Hours!$A$2:$A$13189=HR!$A5,IF(Hours!$B$2:$B$13189="SNRENG",Hours!$D$2:$D$13189,),))

 

[MrKowz]

Re: Using OR statement in FormulaArray

Try using a SUMPRODUCT - you can emulate OR by adding conditions together inside a double-unary operator:

=SUMPRODUCT(--(Hours!$A$2:$A$13189=HR!$A5),--((Hours!$B$2:$B$13189="ENG")+(Hours!$B$2:$B$13189="SNRENG")),Hours!$D$2:$D$13189)

 

[hoffey]

Re: Using OR statement in FormulaArray

Try using a SUMPRODUCT - you can emulate OR by adding conditions together inside a double-unary operator:

=SUMPRODUCT(--(Hours!$A$2:$A$13189=HR!$A5),--((Hours!$B$2:$B$13189="ENG")+(Hours!$B$2:$B$13189="SNRENG")),Hours!$D$2:$D$13189)

So SUMPRODUCT is not even an array? This would seemingly speed things up quite a bit. Any limitations or quirks I should know about? Really appreciate your help MrKowz...

 

[MrKowz]

Re: Using OR statement in FormulaArray

Actually, SUMPRODUCT can seem a bit misleading. It is not entered as an array (CTRL+SHIFT+ENTER), but it inheritantly calculates as an array - so it is no faster than using an array formula (in fact, it is one of the slowest functions).

The reason why is because SUMPRODUCT actually performs an array operation.

However, despite it being a slow function, it is extremely handy and its pros by far outweigh the cons of its speed.

xlDynamic has a really great writeup and explanation of SUMPRODUCT here: http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[hoffey]

May the new year bring you many exciting opportunities! Thanks again MrKowz, on my way to soak up some new knowledge on SUMPRODUCT.

 

[pgc01]

Hi

Just a remark: You can also add the 2 conditions without the double unary operator. Since you are performing an arithmetic operation the booleans will be automatically converted to number.

 

[MrKowz]

Hi

Just a remark: You can also add the 2 conditions without the double unary operator. Since you are performing an arithmetic operation the booleans will be automatically converted to number.

Hmm - I hadn't thought about this! Guess that I've relied on using the double unary so much that I forgot that little bit.