Excel Count values used in formula

[mohibur]

Hi,
if i had cell value for example
=21+22 answer should be 2 as it has used 2 value in the formula
=41+40+41 answer should be 3 as it has used 3 value in the formula

now in adjacent cell i need the count of values in that formula
[TABLE="width: 500"]
<tbody>[TR]
[TD]=21+22[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]=41+40+41[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[tyija1995]

Hey, do you see the cell as "=21+22" or as 43?

If you see the cell already as "=21+22" then try this:

Function CountPlus(txt As String) As String
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "\+"
        CountPlus = Len(txt) - Len(.Replace(txt, "")) + 1
    End With
End Function

=CountPlus(A1)

Note: This will only work if all of the numbers have + signs (so if you have division etc it won't work), it requires the text to be already FORMULATEXT formatted, so if you see the cell as 43 (instead of =21+22) then try this:

=CountPlus(FORMULATEXT(A1))

Assuming A1 is where your cell value is.

 

[mohibur]

yes its showing as 43
and how formula text can be used

 

[Eric W]

Check out post 3 in this thread:

https://www.mrexcel.com/forum/excel-questions/1098724-count-items-being-calculated-cell.html

 

[Fluff]

For the two examples you've shown, try
=LEN(FORMULATEXT(A2))-LEN(SUBSTITUTE(FORMULATEXT(A2),"+",""))+1

 

[mohibur]

What is formulatext
i am using excel 2010

 

[tyija1995]

OK in that case it is probably easier to define your own UDF that replicates FORMULATEXT

Function FORMULATEXTS(cell As Range)
    FORMULATEXTS = cell.Formula
End Function

Then you can use this and embed it into any of the solutions in this thread.

E.g. if you use my CountPlus function too then if the text is in cell A1:

=CountPlus(FORMULATEXTS(A1))

Returns 3 if for example A1 is "=41+42+40" which shows up as 123.

 

[sandy666]

You can try with PowerQuery (add-in for Excel 2010)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]src[/td][td=bgcolor:#70AD47]CountPlus[/td][td=bgcolor:#70AD47]Evaluate[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]21+22[/td][td=bgcolor:#E2EFDA]

2​

[/td][td=bgcolor:#E2EFDA]

43​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]41+40+41[/td][td]

3​

[/td][td]

122​

[/td][/tr]
[/table]

all you need is replace = sign with nothing in src column
then

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"src"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Custom", each Table.Column([Count],"src")),
    Count = Table.AddColumn(List, "CountPlus", each List.Count(Text.Split([src],"+"))),
    Eval = Table.AddColumn(Count, "Evaluate", each Expression.Evaluate([src])),
    RC = Table.RemoveColumns(Eval,{"src", "Count", "Custom"})
in
    RC[/SIZE]