Adding a column

[roger_home]

Can someone give me a formula (or whatever its called) to add a column that would be set like this:

A1=3000
A2=6200
A3=2250 (1625)
A4=10500
A5=6600 (4566)
etc.

If there is only one number in cell it is counted, if 2, then the one in parentheses is the only one of the 2 counted.

Thanx

[Ken Johnson]

Hi roger,
This is an array formula so commit with Ctrl+Shift+Enter, also edit the
addresses so that all of your column A data are included rather than
the first five only, but don't use A:A.

=SUM(IF(ISERROR(FIND("(",A1:A5)),A1:A5,VALUE(MID(A1:A5,FIND("(",A1:A5)
+1,LEN(A1:A5)-(FIND("(",A1:A5) +1)))))

Ken Johnson

[roger_home]

Sorry, newbie here, you've lost me in the not using the range A1:A5...how do I do it without A:A?

[Ken Johnson]

Hi Roger,
It's an array formula and they don't work on entire column (or row)
addresses such as A:A.
You can however us A1:A65535 which is all of column A bar the last row.
Or, if you know the last row in column A that your data uses then you
can use that row number in the address. eg last value is in row 27500
then use A1:A27500, but it's probably easier to use the "all bar last
row" option
Ken Johnson

[Tom Ogilvy]

Unless you like to drink coffee while you watch your worksheet calculate, it
would not be advisable to address more rows than you need to when using
array formulas.

--
Regards,
Tom Ogilvy



"Ken Johnson" <[email protected]> wrote in message
news:[email protected]...
> Hi Roger,
> It's an array formula and they don't work on entire column (or row)
> addresses such as A:A.
> You can however us A1:A65535 which is all of column A bar the last row.
> Or, if you know the last row in column A that your data uses then you
> can use that row number in the address. eg last value is in row 27500
> then use A1:A27500, but it's probably easier to use the "all bar last
> row" option
> Ken Johnson
>

[Tom Ogilvy]

Nothing like testimony to bolster the point: From a very recent post
subject Help Needed with Sumproduct

>Yeah, I just noticed that I defaulted from rows 2 to 65,535. I limited
>the scope and the refreshes sped up, although calculations still take
>longer than I would think is necessary.

Even though this user is using Sumproduct and sumproduct does not require
array entry, it is nonetheless an array formula.

--
Regards,
Tom Ogilvy


"Tom Ogilvy" <[email protected]> wrote in message
news:%[email protected]...
> Unless you like to drink coffee while you watch your worksheet calculate,
it
> would not be advisable to address more rows than you need to when using
> array formulas.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Ken Johnson" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Roger,
> > It's an array formula and they don't work on entire column (or row)
> > addresses such as A:A.
> > You can however us A1:A65535 which is all of column A bar the last row.
> > Or, if you know the last row in column A that your data uses then you
> > can use that row number in the address. eg last value is in row 27500
> > then use A1:A27500, but it's probably easier to use the "all bar last
> > row" option
> > Ken Johnson
> >
>
>

[Ken Johnson]

Hi Tom,
Thanks again, my experience of array formulas is very limited. I've
never actually used one myself. It's nice to know the pitfalls. Let's
hope Roger does not heed my recommended "easy" option!
Ken Johnson