# Couting numbers inside one cell or a range of cells

#### PartisanStar

##### New Member
Let's say my A1 cell looks like this: 1, 1, 1, 1, 1, 5, 3, 1, 1
My A2 cell looks like this: 1, 2, 1, 1

The numbers are separated by a comma.

I want to count how many times a number 1 appears in a cell A1 (7) and in a range of cells A1:A2 (10).

Can you help me out?

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Let's say my A1 cell looks like this: 1, 1, 1, 1, 1, 5, 3, 1, 1
My A2 cell looks like this: 1, 2, 1, 1

The numbers are separated by a comma.

I want to count how many times a number 1 appears in a cell A1 (7) and in a range of cells A1:A2 (10).
Will there ever be any two (or more) digit numbers between the commas?

If so, would the 1 in either, say, 31 or 18 be counted or not?

Will there ever be any two (or more) digit numbers between the commas?

If so, would the 1 in either, say, 31 or 18 be counted or not?

Hello Rick. That's a good question, thank you for reminding me to explain this.

Yes, sometimes there's a two digit number present and those should not be counted. 11-19 should not be counted as a 1. But if that is too complicated I'm fine with the first option which would also help a lot. I can count out the two digit numbers "by hand" later.

Thank you!

Hi,

Assuming that the values within your strings are always separated by the precise combination of a single comma followed by a single space (as in the examples you give) and that you put e.g. 1 (the value for which you wish to perform the count) in D1, these are your two required formulas:

=(LEN(" "&A1&", ")-LEN(SUBSTITUTE(" "&A1&", "," "&\$D\$1&",","")))/LEN(" "&\$D\$1&",")

=SUMPRODUCT(LEN(" "&A1:A2&", ")-LEN(SUBSTITUTE(" "&A1:A2&", "," "&\$D\$1&",","")))/LEN(" "&\$D\$1&",")

Regards

ingenious solution, Real

You're welcome!

If the number is inside the cell like this
111123111
How is the SUM it?

When you say...

If the number is inside the cell like this
111123111
How is the SUM it?

If you are still trying to count the number of times the digit "1" appears, you could, assuming your 111123111 is in Cell A1, use:

=LEN(A1)-LEN(SUBSTITUTE(A1,1,""))

If you are trying to add up the individual digits within the entire string (e.g. 1+1+1+1+2+3+1+1+1), you could, assuming again that your 111123111 is in Cell A1, use:

=SUM(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))) --> entered as an array formula (Ctrl+Shift+Enter)

Thanks,

AP

Last edited:
Thank you very much Mr. "AP"
The formula works like a charm.

No worries - glad to be of help!

AP (AndyPandy)

Replies
3
Views
129
Replies
10
Views
172
Replies
2
Views
82
Replies
6
Views
172
Replies
3
Views
223

1,221,310
Messages
6,159,173
Members
451,543
Latest member
cesymcox

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back