Using wildcard to find number

[toongal12]

I am trying to find the total amount of times a number appears if the first digit is the same. I keep getting 0 or #VALUE.


From a list, I only care about counting the first digit


Example:
A1: 1.2.3
A2: 1.3.4
A3: 2.3.4
A4: 2.5.8
A5: 2.2.2
A6: 3.4.7


So, how many times is 1 the first digit? Two times. How many times is 2 the first digit? Three times. How many times is 3 the first digit? One time.


I tried using a sum with LEFT(A1:A6,1)=1, but I just get zero. I tried FIND with 1* and 1&"*", but it gives me #VALUE. I am stuck

 

[Norie]

Try this.

Put 1, 2 and 3 in C1:C3 and put this formula in D1 and copy it down to D3.

=COUNTIF($A$1:$A$6,C1&"*")

 

[toongal12]

Try this.

Put 1, 2 and 3 in C1:C3 and put this formula in D1 and copy it down to D3.

=COUNTIF($A$1:$A$6,C1&"*")

I am receiving bigger numbers that don't make sense for my situation

I have right now a huge SUM(IF...,IF(... and the last if is the IF-statment of the three digit example. Countif at the end gives me the large number.

I have

SUM(IF...,IF(...,COUNTIF($A$1:$A$6,C1&"*"),0))

 

[sylliec]

I am trying to find the total amount of times a number appears if the first digit is the same. I keep getting 0 or #VALUE.


From a list, I only care about counting the first digit


Example:
A1: 1.2.3
A2: 1.3.4
A3: 2.3.4
A4: 2.5.8
A5: 2.2.2
A6: 3.4.7


So, how many times is 1 the first digit? Two times. How many times is 2 the first digit? Three times. How many times is 3 the first digit? One time.


I tried using a sum with LEFT(A1:A6,1)=1, but I just get zero. I tried FIND with 1* and 1&"*", but it gives me #VALUE. I am stuck

LEFT is a text function, if A1:A6 are numbers you should covert to text before you use the LEFT function. You can convert the result back to a value using the VALUE function in order to count it.

 

[Norie]

When I try the formula I posted I get the expected results you posted.

 

[toongal12]

When I try the formula I posted I get the expected results you posted.

The formula is nested inside a bigger formula, though. Does it still work for you nested?

 

[Norie]

What exactly are you trying to count?

 

[toongal12]

What exactly are you trying to count?

I have a SUM(IF..(IF.. that is trying to find the total number of times a certain sequence occurs. The last IF is for the #.#.#, which is where it has trouble.

Not Specificl Example:
=SUM(IF(thisNumber=1, IF(thatNumber=3, IF(blahNumber=9....COUNTIF($A$1:$A$6,C1&"*"),0))

I am trying to find the total number of times this scenario will occur. The only part giving trouble is the #.#.# number. Otherwise, the code works fine finding the sequence

 

[toongal12]

bump