Formula Bar F9 Result differs from cell result???
 

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&"S636 4002","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???

Formula Bar F9 Result differs from cell result???
 

Enter the formula as an array.

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

--
Biff
Microsoft Excel MVP


"Aaron" wrote in message
...
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&"S636 4002","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???

Formula Bar F9 Result differs from cell result???
 

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" wrote:

Enter the formula as an array.

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

--
Biff
Microsoft Excel MVP


"Aaron" wrote in message
...
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&"S636 4002","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???

Formula Bar F9 Result differs from cell result???
 

Is it the nested Right() function that is casusing the
need to use an array?

Yes, when combined within the IF function.

--
Biff
Microsoft Excel MVP


"Aaron" wrote in message
...
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" wrote:

Enter the formula as an array.

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

--
Biff
Microsoft Excel MVP


"Aaron" wrote in message
...
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&"S636 4002","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???