CODEWITHSUNDEEP
excel
WolfieeifloW
WolfieeifloW

Reputation: 619

Count How Many Times a Number Occurs in a Cell

I have a formula I am using to count the number of times a value appears in a cell, but the issue is if the cell contains a double digit number it counts it multiple times.

Ex: if I have a cell that contains 2 2 2 2 14 it sees 7 numbers - 2 2 2 2 1 4 14 when I only want it to see 2 2 2 2 14.

I am using the formula =((LEN(E35)-LEN(SUBSTITUTE(E35,"2","")))/LEN("2")*$B$2)+((LEN(E35)-LEN(SUBSTITUTE(E35,"3","")))/LEN("3")*$B$3)+... (continues on for each number up to 22).

How do I make this see 14 as one number, not three?

Here's a picture to hopefully help explain. As you can see below, the "wrong val" is adding (2800*4)+7700+41000 when it should only be adding (2800*4)+41000.

enter image description here

Error with Scott Craner's formula:

enter image description here

Upvotes: 0

Views: 750

Answers (1)

Scott Craner
Scott Craner

Reputation: 152605

If there are only numbers then just count the spaces and add 1

=LEN(E35)-LEN(SUBSTITUTE(E35," ","")) + 1

enter image description here

Per your edit:

=SUMPRODUCT(LOOKUP(--TRIM(MID(SUBSTITUTE(E35," ",REPT(" ",99)),(ROW($A$1:INDEX(A:A,LEN(E35)-LEN(SUBSTITUTE(E35," ","")) + 1))-1)*99+1,99)),$B$2:$B$18,$C$2:$C$18))

enter image description here

Upvotes: 3

Related Questions