Couting numbers inside one cell or a range of cells

Rick Rothstein

MrExcel MVP

Joined

Apr 18, 2011

Messages

38,463

Office Version

1. 365

Platform

1. Windows

• Aug 28, 2014

• #2

PartisanStar said:

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).

Click to expand...

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?

 

Upvote 0

P

PartisanStar

New Member

Joined

Aug 28, 2014

Messages

8

• Aug 28, 2014

• #3

Rick Rothstein said:

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?

Click to expand...

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!

 

Upvote 0

XOR LX

Well-known Member

Joined

Jul 2, 2012

Messages

4,517

• Aug 28, 2014

• #4

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

 

Upvote 0

shimaa01234

Active Member

Joined

Jun 24, 2014

Messages

446

• Aug 28, 2014

• #5

ingenious solution, Real

 

Upvote 0

XOR LX

Well-known Member

Joined

Jul 2, 2012

Messages

4,517

• Aug 28, 2014

• #6

You're welcome!

 

Upvote 0

shimaa01234

Active Member

Joined

Jun 24, 2014

Messages

446

• Aug 28, 2014

• #7

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

 

Upvote 0

A

AndyPandy27

Board Regular

Joined

Jul 17, 2012

Messages

142

• Aug 28, 2014

• #8

When you say...

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

Click to expand...

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: Aug 28, 2014

Upvote 0

shimaa01234

Active Member

Joined

Jun 24, 2014

Messages

446

• Aug 28, 2014

• #9

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

 

Upvote 0

A

AndyPandy27

Board Regular

Joined

Jul 17, 2012

Messages

142

• Aug 28, 2014

• #10

No worries - glad to be of help!

AP (AndyPandy)

 

Upvote 0