Count numbers being added in a formula I inherited a massive spreadsheet that ha
ID: 3561494 • Letter: C
Question
Count numbers being added in a formula
I inherited a massive spreadsheet that has a lot of historical data. In looking at ways to improve the overall data of the spreadsheet I need to go through all the historical fields and add a record count, BUT the way that the formulas were done is not exactly what I would have done.
The field that I need to clean up and get a record count is a sum of payments field, but I need to count number number of entries in that field. An example of the formula is =1202.5+130+227.5+259+370+65+185. What I need is go have a field next to this that counts the numbers being added, in this case the result would be 7.
Is this possible?
Explanation / Answer
:Let's say you have such a formula in A2.
Select cell B2.
On the Formulas tab of the ribbon, click Define Name.
Enter Formula as name.
In the Refers to box, enter the formula =GET.CELL(6,A2)
Click OK.
Enter the following formula in cell B2:
=LEN(Formula)-LEN(SUBSTITUTE(Formula,"+",""))+1
This formula can be filled down.