Couting numbers inside one cell or a range of cells

[PartisanStar]

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?

 

[Rick Rothstein]

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?

 

[PartisanStar]

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!

 

[XOR LX]

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

 

[shimaa01234]

ingenious solution, Real

 

[XOR LX]

You're welcome!

 

[shimaa01234]

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

 

[AndyPandy27]

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

 

[shimaa01234]

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

 

[AndyPandy27]

No worries - glad to be of help!

AP (AndyPandy)