# Count the Number of Addends in a Cell

#### dwgnome

##### Active Member
Looking for a formula that will count the number of numbers being added in a cell.

Example: =56+4+9+23+11+155+8222, the result should be 7.
=7+0.2+8+0+dog+3, the result should be 4.

If possible I want it to ignore zeroes and text.

Thanks

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### VaraK

##### Active Member
Hi,

Not sure, if we can do this via a formula. But can surely be done by using macro. If you are interested I can post it for you.

#### njimack

##### Well-known Member
The below UDF will take care of the numbers being added and the zeros. Handling text is still challenging me.

Code:
``````Function Count_Formula(r As Range)

Dim Plus As Integer
Dim Zero As String

Plus = Len(r.Formula) - Len(WorksheetFunction.Substitute(r.Formula, "+", "")) + 1
Zero = Len(r.Formula) - Len(WorksheetFunction.Substitute(r.Formula, "0", ""))

Count_Formula = Plus - Zero

End Function``````

#### dwgnome

##### Active Member
Hi,

Thanks for the UDF solution. I tried it on some real data and it gave inconsistent results. Mostly undercounting by one. On the example I gave in the original post it worked. Here is a sample of the real data and the result from using Count_Formula:

<TABLE style="WIDTH: 248pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=413 border=0 x:str><COLGROUP><COL style="WIDTH: 197pt; mso-width-source: userset; mso-width-alt: 9581" width=328><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=85><TBODY><TR style="HEIGHT: 12.75pt" height=21><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 197pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=328 height=21 x:str="'=117+109">=117+109</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 51pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right width=85 x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=21><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=21 x:str="'=98+79+99+102">=98+79+99+102</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>3</TD></TR><TR style="HEIGHT: 12.75pt" height=21><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=21 x:str="'=100+106+107+49+123+75+96+113">=100+106+107+49+123+75+96+113</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>4</TD></TR><TR style="HEIGHT: 12.75pt" height=21><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=21 x:str="'=99+29+6+106+98+103+6">=99+29+6+106+98+103+6</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>5</TD></TR><TR style="HEIGHT: 12.75pt" height=21><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=21 x:str="'=87">=87</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=21><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=21 x:str="'=84+65+89+85+142+146+104+131+125">=84+65+89+85+142+146+104+131+125</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>8</TD></TR><TR style="HEIGHT: 12.75pt" height=21><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=21 x:str="'=108+112+117+133+16+88+93">=108+112+117+133+16+88+93</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>6</TD></TR><TR style="HEIGHT: 12.75pt" height=21><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=21 x:str="'=56+4+9+23+11+155+8222">=56+4+9+23+11+155+8222</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>7</TD></TR><TR style="HEIGHT: 12.75pt" height=21><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=21 x:str="'=7+0.2+8+0+"dog"+3">=7+0.2+8+0+"dog"+3</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>4</TD></TR><TR style="HEIGHT: 12.75pt" height=21><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=21 x:str="'=182+125+100">=182+125+100</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=21><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=21 x:str="'=89+91+129">=89+91+129</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>3</TD></TR><TR style="HEIGHT: 12.75pt" height=21><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=21 x:str="'=39+98+125+110+94+46">=39+98+125+110+94+46</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>5</TD></TR></TBODY></TABLE>

It seems to count the number of plus signs (+), and subtract numbers that have a zero in it. Is there a way to fix that?

Willing to work on this UDF, but prefer to use a formula approach.

Bump.

Bump.

#### njimack

##### Well-known Member
I've made a small adjustment - the previous version was recognising numbers that contain zero (e.g 101) as zero. The below code should fix this. Still not sure how to deal with text.

Code:
``````Function Count_Formula(r As Range)

Dim Plus As Integer
Dim Zero As String

Plus = Len(r.Formula) - Len(WorksheetFunction.Substitute(r.Formula, "+", "")) + 1
Zero = Len(r.Formula) - Len(WorksheetFunction.Substitute(r.Formula, "+0", ""))

Count_Formula = Plus - Zero

End Function``````

#### pgc01

##### MrExcel MVP
Hi

Another option:

Code:
``````Function CountFormula(r As Range) As Long
Static regex As Object

If regex Is Nothing Then
Set regex = CreateObject("VBScript.RegExp")
regex.Global = True
regex.Pattern = "(\=|\+)(?!0+(\+|\$))\d+(\.\d+)?(?=(\+|\$))"
End If

CountFormula = regex.Execute(r.Formula).Count
End Function``````

Some Tests.

Count 0 for empty cell and:
<TABLE style="WIDTH: 158pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=210 x:str><COLGROUP><COL style="WIDTH: 158pt; mso-width-source: userset; mso-width-alt: 7680" width=210><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 158pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" id=td_post_2634807 class=xl22 height=17 width=210>=dog+0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl22 height=17>=0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl22 height=17>=dog</TD></TR></TBODY></TABLE>

Count 1 for:
<TABLE style="WIDTH: 158pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=210 x:str><COLGROUP><COL style="WIDTH: 158pt; mso-width-source: userset; mso-width-alt: 7680" width=210><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 158pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" id=td_post_2634807 class=xl24 height=17 width=210>=0+1.23</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 height=17>=1.23+dog</TD></TR></TBODY></TABLE>

Count 2 for:
<TABLE style="WIDTH: 158pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=210 x:str><COLGROUP><COL style="WIDTH: 158pt; mso-width-source: userset; mso-width-alt: 7680" width=210><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 158pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" id=td_post_2634807 class=xl24 height=17 width=210>=0.1+1.23+dog</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 height=17>=0+1.23+dog+0.1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 height=17>=0+1.23+dog+0.1+0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 height=17>=0.1+1+dog</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 height=17>=0.1+1+dog+0</TD></TR></TBODY></TABLE>

Count 3 for:
=0.1+1+dog+0.1

Count 7 for:
<TABLE style="WIDTH: 158pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=210 x:str><COLGROUP><COL style="WIDTH: 158pt; mso-width-source: userset; mso-width-alt: 7680" width=210><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 158pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 height=17 width=210 u1:str="'=99+29+6+106+98+103+6">=99+29+6+106+98+103+6</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 height=17>=dog+99+29+6+106+98+103+6+0</TD></TR></TBODY></TABLE>

#### dwgnome

##### Active Member
Thank you Neil and PGC01.

I'll have to try them when I get in after the weekend.

No chance for a formula version of the UDF?

#### pgc01

##### MrExcel MVP
Hi

Here's a formula solution.

Define the name

Name: FormulaL1
Refers to: =GET.CELL(6,INDIRECT("RC[-1]",FALSE))

Assuming the cells with the formulas in column A, in B2:

=SUM(IF(ISNUMBER(MATCH(MID(FormulaL1,ROW(INDIRECT("1:"&LEN(FormulaL1)-1)),1),{"=","+"},0)),IF(ISNUMBER(-MID(FormulaL1,1+ROW(INDIRECT("1:"&LEN(FormulaL1)-1)),1)),0+IF(MID(FormulaL1,1+ROW(INDIRECT("1:"&LEN(FormulaL1)-1)),1)<>"0",1,MID(FormulaL1,2+ROW(INDIRECT("1:"&LEN(FormulaL1)-1)),1)="."))))

This is an array formula. You have to confirm it with CTRL-SHIFT-ENTER.

Ex.:

I display the formulas in the the cells in column A in the corresponding cells in column D so that you can check the results.

Remark: the udf solution is not only easier to use, it is also more efficent.

Replies
6
Views
394
Replies
6
Views
285
Replies
32
Views
297
Replies
4
Views
219
Replies
3
Views
243

1,191,578
Messages
5,987,413
Members
440,096
Latest member
yanaungmyint

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back