Complex Formula help.

[Ian Betteridge]

I'm using a formula to find duplicated orders for individual m/c within a half hour period.

=IF(A878="","",IF(AND(B878<>"101",B878<>"102"),"",IF(F878="","",IF(SUMPRODUCT(--($C$2:$C$5001=$C878)*--($F$2:$F$5001<=(F878+0.5/24))*--($F$2:$F$5001>=(F878-0.5/24)))>1,"Check Order",""))))

The latter part of the formula (SUMPRODUCT) I only want to calculate if either 101 or 102 is in the corresponding cell in column B. (101 & 102 are in text format)

Could someone please assist with juggling the formula to get this result.

Much appreciated
Ian

 

[Andrew Poulsom]

Try:

=IF(OR(A878="",AND(B878<>"101",B878<>"102"),F878=""),"",IF(SUMPRODUCT((($B$2:$B$5001="101")+($B$2:$B$5001="102")),--($C$2:$C$5001=$C878),--($F$2:$F$5001<=(F878+0.5/24)),--($F$2:$F$5001>=(F878-0.5/24)))>1,"Check Order",""))

 

[Ian Betteridge]

Brilliant Andrew.
Thank You