changing formula
 

This is my formula for adding up numbers in a column.

=SUM(IF(ISERR(FIND("-",J20:J137)),,REPLACE(J20:J137,1,FIND("-",J20:J137),"")-LEFT(J20:J137,FIND("-",J20:J137)-1)+1))

I have extended the length of the column to J20 to J183 as I now have more
entries, if I my manually change the J137 to J183 then the formula no longer
works.
Why is this? I dont understand the formula as it was done for me.

What am I doing wrong.

thanks

changing formula
 

Make your alteration to the formula then with the cursor still in the formula
bar enter the new formula with:-

Ctrl+Shift+Enter

Mike

"S S" wrote:

This is my formula for adding up numbers in a column.

=SUM(IF(ISERR(FIND("-",J20:J137)),,REPLACE(J20:J137,1,FIND("-",J20:J137),"")-LEFT(J20:J137,FIND("-",J20:J137)-1)+1))

I have extended the length of the column to J20 to J183 as I now have more
entries, if I my manually change the J137 to J183 then the formula no longer
works.
Why is this? I dont understand the formula as it was done for me.

What am I doing wrong.

thanks

changing formula
 

It is an array formula, which means that when you type it in or
subsequently edit it then you must use CTRL-SHIFT-Enter to commit it
rather than the usual Enter. If you do this correctly then Excel will
wrap the formula in curly braces { } when viewed in the formula bar -
you must not type these yourself.

Hope this helps.

Pete

On Aug 30, 8:18 am, "S S" wrote:
This is my formula for adding up numbers in a column.

=SUM(IF(ISERR(FIND("-",J20:J137)),,REPLACE(J20:J137,1,FIND("-",J20:J137),""*)-LEFT(J20:J137,FIND("-",J20:J137)-1)+1))

I have extended the length of the column to J20 to J183 as I now have more
entries, if I my manually change the J137 to J183 then the formula no longer
works.
Why is this? I dont understand the formula as it was done for me.

What am I doing wrong.

thanks

changing formula
 

It's an array formula. It needs to be committed with Ctrl + Shift + Enter -
i.e. after making the changes, exit edit mode with this key combination.

Greg

"S S" wrote:

This is my formula for adding up numbers in a column.

=SUM(IF(ISERR(FIND("-",J20:J137)),,REPLACE(J20:J137,1,FIND("-",J20:J137),"")-LEFT(J20:J137,FIND("-",J20:J137)-1)+1))

I have extended the length of the column to J20 to J183 as I now have more
entries, if I my manually change the J137 to J183 then the formula no longer
works.
Why is this? I dont understand the formula as it was done for me.

What am I doing wrong.

thanks

changing formula
 

Actually, a quicker way to make the changes is to highlight the
cell(s) with the formula in and do Find & Replace (CTRL-H):

Find what: J137
Replace with: J183

then click Replace All. This way you do not need to use CSE as you are
not strctly editing the formula - it assumes that the formula works in
the first place.

Hope this helps.

Pete

On Aug 30, 9:25 am, Pete_UK wrote:
It is an array formula, which means that when you type it in or
subsequently edit it then you must use CTRL-SHIFT-Enter to commit it
rather than the usual Enter. If you do this correctly then Excel will
wrap the formula in curly braces { } when viewed in the formula bar -
you must not type these yourself.

Hope this helps.

Pete

On Aug 30, 8:18 am, "S S" wrote:



This is my formula for adding up numbers in a column.

=SUM(IF(ISERR(FIND("-",J20:J137)),,REPLACE(J20:J137,1,FIND("-",J20:J137),""**)-LEFT(J20:J137,FIND("-",J20:J137)-1)+1))

I have extended the length of the column to J20 to J183 as I now have more
entries, if I my manually change the J137 to J183 then the formula no longer
works.
Why is this? I dont understand the formula as it was done for me.

What am I doing wrong.

thanks- Hide quoted text -

- Show quoted text -

changing formula
 

This is my formula for adding up numbers in a column.

=SUM(IF(ISERR(FIND("-",J20:J137)),,REPLACE(J20:J137,1,FIND("-",J20:J137),"")-LEFT(J20:J137,FIND("-",J20:J137)-1)+1))

I have extended the length of the column to J20 to J183 as I now have more
entries, if I my manually change the J137 to J183 then the formula no
longer works.
Why is this? I dont understand the formula as it was done for me.

What am I doing wrong.

Because there is a range of cells being covered, you have an array
formula... you need to commit the formula using Ctrl+Shift+Enter. To do
this, select the cell **then** put the cursor into the formula bar... then
press Ctrl+Shift+Enter and the formula should work again.

Rick

changing formula
 

Thanks to all who answered, problem now sorted.
thanks for all your help.


"Rick Rothstein (MVP - VB)" wrote in
message ...
This is my formula for adding up numbers in a column.

=SUM(IF(ISERR(FIND("-",J20:J137)),,REPLACE(J20:J137,1,FIND("-",J20:J137),"")-LEFT(J20:J137,FIND("-",J20:J137)-1)+1))

I have extended the length of the column to J20 to J183 as I now have
more entries, if I my manually change the J137 to J183 then the formula
no longer works.
Why is this? I dont understand the formula as it was done for me.

What am I doing wrong.

Because there is a range of cells being covered, you have an array
formula... you need to commit the formula using Ctrl+Shift+Enter. To do
this, select the cell **then** put the cursor into the formula bar... then
press Ctrl+Shift+Enter and the formula should work again.

Rick