Why do I get correct answers to formula then #NAME for some no's

[James W.]

I have an Excel worksheet with six separate columns of numbers and several
rows with numbers in one, several or all of the columns plus a total column.

I am using a formula to crosscheck the totals. The formula being used is
=IF(SUM(S4:S22)=SUM(B23:Q23),SUM(S4:S22),value). All of the numbers down and
across (both positive and negative totals) add to a correct total in cell
S23. Suddenly, by adding a negative number (within a range of -45 to -244)
the answer becomes #NAME. Why?? By adding any other numbers (positive or
negative) the crosscheck answer is correct.

I have never had this problem with an Excel spreadsheet previously.

 

[Pete_UK]

Is value a named range somewhere, or do you want this to appear as
text to alert you to the fact that the two totals are not the same? If
the latter, then you need to enclose the word value within double
quotes, like so:

=IF(SUM(S4:S22)=SUM(B23:Q23),SUM(S4:S22),"value")

Hope this helps.

Pete