Formula Bar F9 Result differs from cell result???

[Aaron]

This is weird. This formula returns a zero in the cell:

=SUMPRODUCT((IF($C$5<>"*",'Trial Balance'!$I$2:$I$65536,RIGHT('Trial
Balance'!$I$2:$I$65536,8))=IF($C$5<>"*",$C$5&"S6364002","S6364002"))*('Trial
Balance'!$J$2:$J$65536=U$6)*('Trial Balance'!$E$2:$E$65536))

But if I highlight the formula in the formula bar and hit F9 it returns
106934.03

106934.03 is the correct answer! Why would I be getting a zero in the
cell???

 

[T. Valko]

Enter the formula as an array.

Array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Aaron]

Thank you, I will. I use variations of Sumproduct all the time and never had
to before. Is it the nested Right() function that is casusing the need to
use an array?

 

[T. Valko]

Is it the nested Right() function that is casusing the

need to use an array?

Yes, when combined within the IF function.