Counting number of items being added in a formula

L

Lambtwo

I want to count the number of items that are being added together in a cell.

For example if A1 had =3+5+7, A1 would sum to 15 and I would want B1 to show
3 for the number of items being added.

What is the best way to capture this?
 
S

ScottO

It's going to depend a lot on the exact formula that you're using to
add the numbers.
Can you show an example of the formula please?
If there is more than one possible formula style, then please show an
example of each style.
Rgds,
ScottO

| I want to count the number of items that are being added together
in a cell.
|
| For example if A1 had =3+5+7, A1 would sum to 15 and I would want
B1 to show
| 3 for the number of items being added.
|
| What is the best way to capture this?
|
|
|
|
 
L

Lambtwo

This is for a retail sale where we are trying to count the number of items
sold. Each item sold is input directly into A1. Example, if 28 items were
sold, then A1 would have the sale prices like =3.00+7.25+5.50, etc ..... We
want to have the count of items sold (28) based on the entry in A1 to appear
in B1.
 
S

ScottO

I'm sorry but I can't think of a formula way of doing this. Perhaps
one of the 'more able' regular posters will find a way.
I'm sure that it could be done by writing a function, but I can't
help you with that.
Depending on what the rest of your spreadsheet looks like, a
workaround would be to switch the 'Move selection after Enter'
setting to "Down", then enter all the item prices down columnA, then
put =SUM(A:A) in B1, and =Count(A:A) in B2. Pretty lame I know, but
it would get the job done if you don't get a better offer.
If layout matters, you could hide all that ugly stuff on another tab,
and just reference the B1 & B2 cells onto your output tab.
If all the entries are down columnA, when its time for the next
transaction, just select the whole of ColA and hit delete.
HTH,
ScottO


| This is for a retail sale where we are trying to count the number
of items
| sold. Each item sold is input directly into A1. Example, if 28
items were
| sold, then A1 would have the sale prices like =3.00+7.25+5.50, etc
...... We
| want to have the count of items sold (28) based on the entry in A1
to appear
| in B1.
|
|
message
| | > It's going to depend a lot on the exact formula that you're using
to
| > add the numbers.
| > Can you show an example of the formula please?
| > If there is more than one possible formula style, then please
show an
| > example of each style.
| > Rgds,
| > ScottO
| >
| > | > | I want to count the number of items that are being added
together
| > in a cell.
| > |
| > | For example if A1 had =3+5+7, A1 would sum to 15 and I would
want
| > B1 to show
| > | 3 for the number of items being added.
| > |
| > | What is the best way to capture this?
| > |
| > |
| > |
| > |
| >
| >
|
|
 
B

Bob Phillips

Here is a simple function

Function CountList(rng As Range, Optional delimiter As String = "+")
Dim arg
If rng.Count > 1 Then
CountList = CVErr(xlErrRef)
Else
arg = Split(rng.Formula, delimiter)
CountList = UBound(arg) - LBound(arg) + 1
End If
End Function


--

HTH

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

Max

Hi Bob,

I wasn't able to get your UDF to work in my Excel 97

In B1: =countlist(A1)
returns a compile error (sub or function not defined)

and "Split" was highlighted in the line:
arg = Split(rng.Formula, delimiter)

Any work-arounds?

Thanks
 
B

Bob Phillips

That's because you are still in the dark ages Max, still using XL97 <vbg>.

Try this version

Function CountList(rng As Range, Optional Delimiter As String = "+")
Dim arg
If rng.Count > 1 Then
CountList = CVErr(xlErrRef)
Else
arg = Split(rng.Formula, Delimiter)
CountList = UBound(arg) - LBound(arg) + 1
End If
End Function

#If VBA6 Then
#Else
'-----------------------------------------------------------------
Function Split(Text As String, _
Optional Delimiter As String = ",") As Variant
'-----------------------------------------------------------------
Dim i As Long
Dim sFormula As String
Dim aryEval
Dim aryValues

If Delimiter = vbNullChar Then
Delimiter = Chr(7)
Text = Replace(Text, vbNullChar, Delimiter)
End If
sFormula = "{""" & Application.Substitute(Text, Delimiter, """,""") &
"""}"
aryEval = Evaluate(sFormula)
ReDim aryValues(0 To UBound(aryEval) - 1)
For i = 0 To UBound(aryValues)
aryValues(i) = aryEval(i + 1)
Next

Split = aryValues

End Function
#End If


--

HTH

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

Max

Thanks, Bob but urrgh ... got hit again, same error,
but this time at this line in "Function Split":
Text = Replace(Text, vbNullChar, Delimiter)
Click to expand...
"Replace" was highlighted

Am I ever going to see the "light" <g>?
Thanks
 
R

Roger Govier

Hi Bob

That works perfectly (XL2003) and does exactly what the OP requested.
If there were mixed operators, however, is there a way of tackling that with
a modification to the UDF?

Regards

Roger Govier
 
B

Bob Phillips

Doh! Missed that bit

Option Explicit

Function CountList(rng As Range, Optional delimiter As String = "+")
Dim arg
If rng.Count > 1 Then
CountList = CVErr(xlErrRef)
Else
arg = Split(rng.Formula, delimiter)
CountList = UBound(arg) - LBound(arg) + 1
End If
End Function

#If VBA6 Then
#Else
'-----------------------------------------------------------------
Function Split(Text As String, _
Optional delimiter As String = ",") As Variant
'-----------------------------------------------------------------
Dim i As Long
Dim sFormula As String
Dim aryEval
Dim aryValues

If delimiter = vbNullChar Then
delimiter = Chr(7)
Text = Replace(Text, vbNullChar, delimiter)
End If
sFormula = "{""" & Application.Substitute(Text, delimiter, """,""") &
"""}"
aryEval = Evaluate(sFormula)
ReDim aryValues(0 To UBound(aryEval) - 1)
For i = 0 To UBound(aryValues)
aryValues(i) = aryEval(i + 1)
Next

Split = aryValues

End Function


'-----------------------------------------------------------------
Function Replace(expression As String, _
find_string As String, _
replacement As String)
'-----------------------------------------------------------------
Dim i As Long
Dim iLen As Long
Dim iNewLen As Long
Dim sTemp As String


sTemp = expression
iNewLen = Len(find_string)
For i = 1 To Len(sTemp)
iLen = Len(sTemp)
If Mid(sTemp, i, iNewLen) = find_string Then
sTemp = Left(sTemp, i - 1) & replacement & Right(sTemp, iLen -
i - iNewLen + 1)
i = i + iNewLen - 1
End If
Next i

Replace = sTemp

End Function
#End If


--

HTH

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

Bob Phillips

Hi Roger,

I did do that once before. I will see if I can dig it out.

--

HTH

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

Lambtwo

Thanks for your help.

Please excuse my ignorance .... how do I set a function up in my file?
 
B

Bob Phillips

Go to the VBIDE, Alt-F11, and in there insert a module, Insert>Module, and
paste the code in there.

When you open that workbook in future, if your security setting is medium,
it will prompt to ask if you want to open a book with macros.

--

HTH

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

Lambtwo

Thank You, works great!


Bob Phillips said:
Go to the VBIDE, Alt-F11, and in there insert a module, Insert>Module, and
paste the code in there.

When you open that workbook in future, if your security setting is medium,
it will prompt to ask if you want to open a book with macros.

--

HTH

RP
(remove nothere from the email address if mailing direct)
Click to expand...
 
Top