Count number of inputs


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
 
Ad

Advertisements

G

Gary''s Student

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
 
Ad

Advertisements

Q

Question Boy

Thank you, with a minor mod (for my needs - ignore blank cells) it worked
like a charm!

QB
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top