1. Hi To All,

I am trying to count or tally how often a number or numbers appear in an excel chart. In some cases the cells contain text as well and others just numbers usually separated by a comma or a dash. I've attached a sample of my excel chart. The data as shown will always appear in a single excel column. I can manage the counting of numbers, but when text is also present I am unable to solve it.

Thank you,
Marty

2. Not sure exactly what you are after, but to count (for example) the number of 1s in the range A1:A6 you can use the array formula (confirm with ctrl-shift-enter):

=SUM(LEN(\$A1:\$A6)-LEN(SUBSTITUTE(\$A1:\$A6,1,"")))

For the 2s
=SUM(LEN(\$A1:\$A6)-LEN(SUBSTITUTE(\$A1:\$A6,2,"")))

Etc all the way to 0

Steve

3. It would definitively help if you could tell us what is the expected result in your example.

4. Hi To All,

Sorry for not being more explicit..let me try to explain. If I have a column in excel a range say A1:A220 with some cells containing a series of numbers separated by commas, and in other rows or cells I have some text along with numbers, and I wish to tally how many times within this cell range that for example the number 8 appears of the number 256 appears. The results of this search or query could be placed in one or several cells on the same worksheet.

hopefully this helps...

Thanks again...

Marty

5. What if the number 8 is part of a bigger number, like 18?
Should it be counted then?

6. parse your list using TEXT TO COLUMNS (using a comma delimeter), then use the FREQUENCY function to perform the count

7. Perhaps if you provided a few sample columns with data and the exact results that you want for each one we may be able to provide a formula or user defined function...

Steve

8. Hi to All,

A number will be counted if it is separated by a comma , or a hyphen -. All the numbers pertaining to or contained in the date cells can be ignored, such as cells A1, A4, A7 etc.

Regards,
Marty

9. I am still not clear on your input and what you want the output to look like. It would be very useful to attach a sample workbook with what you have and the results you want based on that example. If the input can be dramatically different, you may want to include several different test inputs and what the outputs would be to ensure that the solution meets all the contingencies...

Steve

10. [attachment=87918:wopr ex2.xls]I do not think I was totally clear either. Attached is a clearer representation of what I am trying to do.....count the frequency of numbers in a specified cell range.

Hopefully this helps out.

Thank You for for patience and assistance....

Regards,
Marty

11. In your example you wrote: "that appear only in the following cells in this sequence: A2,A3, A5, A6, A8,A9,A11,A12,A14,A15….A150". What is the logic to not consider the cells A4, A7, A10, A13? Is it because they start with the text "Shift"? In such case, maybe we could consider having a second column like =IF(Left(A4,5)="Shift","",A4), then you can apply the future function on this new column. As it also seems that all the dates are in these cells starting with "Shift", we eliminate the issue of defining whether a number is part of a date or not. Finally, if a number is always surrounded by an hypen, a blank or a comma, then "-"&SUBSTITUTE(SUBSTITUTE(A4,",","-")," ","-")&"-" will replace all the separators by an hyphen so each now is between two hyphens. So now, with the use of the FIND function which returns an error if a text is not found within another text, and using a function array, the automagic formula is:
Code:
`=SUM(IF(ISERR(FIND("-"&E2&"-","-"&SUBSTITUTE(SUBSTITUTE(IF(LEFT(\$A\$1:\$A\$150,5)="Shift","",\$A\$1:\$A\$150),",","-")," ","-")&"-")),0,1))`
where E2 is the number to look for. Validate the formula above with Ctrl-Shift-Enter to create an array formula.

EDIT: This formula doesn't take into account the multiple presence of the same number in one cell...

12. Perhaps the array-formula (confirm with ctrl-shift-enter)?

=SUM(IF(LEFT(\$A\$1:\$A\$150,5)<>"Shift",(LEN(\$A\$1:\$A\$ 150)-LEN(SUBSTITUTE(\$A\$1:\$A\$150,E2,"")))))/LEN(E2)

Steve

PS the above will give the total of the numbers which may not be what you want (if it has a 1 and 11, it will find 3 1s for example)

If you want to distinguish the individual value 1 and the digits 1 in numbers like 10-19, 21, 31, etc then you can try this formula which assumes commas are delimiters (after changing spaces and dashes to commas). Confirm with ctrl-shift-enter:

=SUM(IF(LEFT(\$A\$1:\$A\$150,5)<>"Shift",(LEN(","&\$A\$1 :\$A\$150&",")-LEN(SUBSTITUTE((","&SUBSTITUTE(SUBSTITUTE(\$A\$1:\$A\$ 150,"-",",")," ",",")&","),","&E17&",","")))))/LEN(","&E17&",")

13. Thank you Stephane and sdckapr,

this may take me a bit to digest, between classes. Yes cells such as 1, 4, 7, 10 can be ignored as they refer to shift...they are n/a for this purpose. Let me try this input out.....thanks for your input and patience with a formual "green horn"

Regards,
Marty

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•