Summing items in a cell

D

dpmoore

How do you SUM items in a cell.
I have a parts list with reference designators like U3-U6.
I want the sum of the range of items, in this case it's 4, but can't
figure out how to do this.
Any help will be appreciated
 
D

Don Guillett

More info and then a formula using find can be used or a udf (macro for
custom formula)
 
B

Bob Phillips

If they are always of that format, try

=RIGHT(A2,LEN(A2)-FIND("-",A2)-1)-MID(A2,2,FIND("-",A2)-2)+1

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
S

swatsp0p

Can you give more detailed information? Is U3-U6 a reference to cells
in the worksheet? (which should be U3:U6, btw) Is this reference
contained in a cell by itself or within the part descriptor/part
number?

More detail, please.
 
D

dpmoore

The reference U3-U6 is contained in the cell. and represents the range
of reference designators to be considered. Another way of listing
reference designators would be: U3, U4, U5, U6. If they were listed
like this it would be easy to count the items. but when a range is
given like U3-U6 I cant figure out how to count them.
 
D

Don Guillett

I may be thick but I still can't see that you have told us what you want to
count.
 
D

Dave Peterson

Maybe a UDF like this:


Option Explicit
Function CountCells(str As String) As Variant
Dim testRng As Range

Set testRng = Nothing
On Error Resume Next
Set testRng = Application.Caller.Parent _
.Range(Application.Substitute(str, "-", ":"))
On Error GoTo 0

If testRng Is Nothing Then
CountCells = CVErr(xlErrRef) 'or just 0??
Else
CountCells = testRng.Cells.Count
End If

End Function

===
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=countcells(A1)
 
Top