Counting number of items being added in a formula
 

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?

Counting number of items being added in a formula
 

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

"Lambtwo" wrote in message
news:K5i9f.370924$tl2.237458@pd7tw3no...
| 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?
|
|
|
|

Counting number of items being added in a formula
 

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.


"ScottO" wrote in 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

"Lambtwo" wrote in message
news:K5i9f.370924$tl2.237458@pd7tw3no...
| 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?
|
|
|
|

Counting number of items being added in a formula
 

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


"Lambtwo" wrote in message
news:0Oi9f.360842$oW2.198240@pd7tw1no...
| 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.
|
|
| "ScottO" wrote in
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
|
| "Lambtwo" wrote in message
| news:K5i9f.370924$tl2.237458@pd7tw3no...
| | 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?
| |
| |
| |
| |
|
|
|
|

Counting number of items being added in a formula
 

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)


"ScottO" wrote in message
...
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


"Lambtwo" wrote in message
news:0Oi9f.360842$oW2.198240@pd7tw1no...
| 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.
|
|
| "ScottO" wrote in
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
|
| "Lambtwo" wrote in message
| news:K5i9f.370924$tl2.237458@pd7tw3no...
| | 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?
| |
| |
| |
| |
|
|
|
|

Counting number of items being added in a formula
 

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Bob Phillips" wrote in message
...
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

Counting number of items being added in a formula
 

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)


"Max" wrote in message
...
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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Bob Phillips" wrote in message
...
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

Counting number of items being added in a formula
 

Thanks, Bob but urrgh ... got hit again, same error,
but this time at this line in "Function Split":

Text = Replace(Text, vbNullChar, Delimiter)
"Replace" was highlighted

Am I ever going to see the "light" <g?
Thanks
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Bob Phillips" wrote in message
...
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

Counting number of items being added in a formula
 

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


Bob Phillips wrote:
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

Counting number of items being added in a formula
 

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)


"Max" wrote in message
...
Thanks, Bob but urrgh ... got hit again, same error,
but this time at this line in "Function Split":

Text = Replace(Text, vbNullChar, Delimiter)
"Replace" was highlighted

Am I ever going to see the "light" <g?
Thanks
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Bob Phillips" wrote in message
...
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

Counting number of items being added in a formula
 

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)


"Roger Govier" wrote in message
...
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


Bob Phillips wrote:
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

Counting number of items being added in a formula
 

Splendid, Bob. Works great !
Thanks for the sunshine <g
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Counting number of items being added in a formula
 

Thanks for your help.

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


"Bob Phillips" wrote in message
...
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)


"ScottO" wrote in message
...
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


"Lambtwo" wrote in message
news:0Oi9f.360842$oW2.198240@pd7tw1no...
| 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.
|
|
| "ScottO" wrote in
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
|
| "Lambtwo" wrote in message
| news:K5i9f.370924$tl2.237458@pd7tw3no...
| | 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?
| |
| |
| |
| |
|
|
|
|

Counting number of items being added in a formula
 

Go to the VBIDE, Alt-F11, and in there insert a module, InsertModule, 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)


"Lambtwo" wrote in message
news:FPdaf.396163$tl2.16646@pd7tw3no...
Thanks for your help.

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


"Bob Phillips" wrote in message
...
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)


"ScottO" wrote in message
...
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


"Lambtwo" wrote in message
news:0Oi9f.360842$oW2.198240@pd7tw1no...
| 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.
|
|
| "ScottO" wrote in
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
|
| "Lambtwo" wrote in message
| news:K5i9f.370924$tl2.237458@pd7tw3no...
| | 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?
| |
| |
| |
| |
|
|
|
|

Counting number of items being added in a formula
 

Thank You, works great!


"Bob Phillips" wrote in message
...
Go to the VBIDE, Alt-F11, and in there insert a module, InsertModule, 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)


"Lambtwo" wrote in message
news:FPdaf.396163$tl2.16646@pd7tw3no...
Thanks for your help.

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


"Bob Phillips" wrote in message
...
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)


"ScottO" wrote in message
...
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


"Lambtwo" wrote in message
news:0Oi9f.360842$oW2.198240@pd7tw1no...
| 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.
|
|
| "ScottO" wrote in
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
|
| "Lambtwo" wrote in message
| news:K5i9f.370924$tl2.237458@pd7tw3no...
| | 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?
| |
| |
| |
| |
|
|
|
|