MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Actual vs. fx results


Posted by Rob Potts on November 15, 2001 2:00 PM

=SUM(IF($B$18:$B$97<=C$4,$R$18:$R$97))
The result for this formula within the cell is the sum of R18:R97 with no adjusment for the condition. When I hit the fx button on my tool bar when the cell is chosen, the result I get in the paste function menu is correct because it takes into account the condition (<=C$4). Why would the results be different? Thanks for any help you can provide.

Rob


Posted by lenze on November 15, 2001 2:40 PM

Just a guess, but are you on manual calculation?

Posted by Richard S on November 15, 2001 4:07 PM

=SUMIF($B$18:$B$97<=C$4,$R$18:$R$97)?? (nt)

Posted by IML on November 15, 2001 4:25 PM

=SUM(IF($B$18:$B$97,&LT;=C$4,$R$18:$R$97))

Try
=SUM(IF($B$18:$B$97," &LT; ="&C$4,$R$18:$R$97))
with no spaces.

Or better yet,
=SUMIF($B$18:$B$97," &LT; ="&C$4,$R$18:$R$97)

again without the spaces around the less than.

It is probably just adding cells that are not equal to the value the text "&LT;C$4"

Posted by Aladin Akyurek on November 15, 2001 4:44 PM

Make that...

=SUMIF($B$18:$B$97, "&LT;="&C$4,$R$18:$R$97)

Aladin

======= : Just a guess, but are you on manual calculation? :


Posted by Rob Potts on November 15, 2001 8:52 PM

Thanks for the suggestions, I'll try them tomorrow at work. In the mean time, does anyone have an inkling as to why the answer in the paste function menu is different that the actual result?

Once again, thanks for the input
Rob