Adding a column

R

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
 
K

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
 
R

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?
 
K

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
 
T

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.
 
T

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.
Click to expand...

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

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
 
Top