Count number of inputs

  • Thread starter Thread starter Question Boy
  • Start date Start date
Q

Question Boy

Hello,

I have a table with 3 rows with a multitude of columns. If we look at one
column, each cell is simply the summation of values (ie: =120+65+15)

So I end up with a a column looking with totals looking like

220
125
347

Is there a awy to, in a cell below the table, have the count of inputs or
elements used in the summations? If with look at the formulas used for the
example above

Ie:
=100+120
=25+75+25
=300+47

Have a cell which would return the value/number 7 (2 elements for the 1st
summation, 3 elements for the 1st summation, 2 elements for the 1st
summation) as the total number of elements.

Thank you,

QB
 
For the simple formulas in your posting, the number of inputs in an
individual cell is just the number of "connectors" ("+" or "-") plus one. We
create a UDF that counts the number of "connectors" in a range of cells and
does the math:

Function IAmTheCount(rr As Range) As Integer
IAmTheCount = 0
For Each r In rr
v = r.Formula
v2 = Replace(Replace(v, "+", ""), "-", "")
n = Len(v) - Len(v2)
IAmTheCount = IAmTheCount + 1 + n
Next
End Function
 
Thank you, with a minor mod (for my needs - ignore blank cells) it worked
like a charm!

QB
 
Back
Top