Summing items in a cell

  • Thread starter Thread starter dpmoore
  • Start date Start date
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
 
More info and then a formula using find can be used or a udf (macro for
custom formula)
 
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)
 
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.
 
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.
 
I may be thick but I still can't see that you have told us what you want to
count.
 
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)
 
Back
Top