I'm trying to add some sort of cross-check to my spreadsheet. I need to make a function that compares the sum of a list of numbers and another number (taken from another sheet). I know something like =IF(SUM(E5:E9)=E4,"CORRECT","WRONG") can help, but what if I insert a new line of info at E:10? That function cannot help. Any ideas?

I'm also looking at Conditional Formatting (to avoid having an extra line in the spreadsheet reading true or false). I now have something like =SUM(E5:E9)<E4 in conditional formatting. I'm stuck with the same error as before. What happens when I insert a new line of data, say E10, and I want to include that in my conditional formatting? And is there an easy way to apply the same conditional formatting stuff to other datasets, such as =SUM(F5:F9)<F4, etc?

"zahoulik" wrote:
=IF(SUM(E5:E9)=E4,"CORRECT","WRONG") can help,
but what if I insert a new line of info at E:10?

There may be more clever solutions, but my approach
is to simply ensure that the SUM() range includes blank
(or text) cells above and below the interesting range.
This is often the case anyway, for aesthetic reasons.

Bewa That can lead to confusing results, if you are
not careful. For example, suppose E4 and E10 are
always-blank cells, and you write SUM(E4:E10). That's
okay. But COUNT(E4:E10) gives a misleading result.
You would have to be careful to write COUNT(E4:E10) - 2.

Hi,

If you are using Office 2003, then you should have ticked "Extend data range
formats and formula" under the edit tab in "options" under the "Tools" menu.

If it is ticked or if you are not using office 2003, then please use the
following array formula (Ctrl+Shift+Enter)

Assume following data is in the range J6:J10 and you have use the sum
funtion in cell J12.

Use the following formual in cell J12,

=SUM(J6:OFFSET(J6,COUNT(J$7:J11),0))

Regards,

Ashish Mathur

"zahoulik" wrote:


I'm trying to add some sort of cross-check to my spreadsheet. I need to
make a function that compares the sum of a list of numbers and another
number (taken from another sheet). I know something like
=IF(SUM(E5:E9)=E4,"CORRECT","WRONG") can help, but what if I insert a
new line of info at E:10? That function cannot help. Any ideas?


--
zahoulik