Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count number of inputs
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count number of inputs
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 -- Gary''s Student - gsnu200769 "Question Boy" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count number of inputs
Thank you, with a minor mod (for my needs - ignore blank cells) it worked
like a charm! QB "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200769 "Question Boy" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count rows and insert number to count them. | Excel Discussion (Misc queries) | |||
count each cell that have a number and take that number and count. | Excel Discussion (Misc queries) | |||
All number inputs are divided by 100 ? | Setting up and Configuration of Excel | |||
Number count - postcodes and need to count the numbers of tim... | Excel Discussion (Misc queries) | |||
Count number of times a specific number is displayed in a cell ran | Excel Worksheet Functions |