Can excel differentiate related numbers

**khank** · 03-10-2011, 10:03 AM

Is there any coding, which will NOT differentiate between numbers, but match and group each 2-digit number, let's say: 12 and 21 because both have a 1 and 2?

Example:

A1: 12
A2: 36
A3: 45
A4: 11
A5: 09
A6: 21
A7: 12

This column is for my "match" number (12 & 21)

C:1 12

This column is for a "countif" function???

D1: 3

The answer for be "3" (A1, A6, A7).

**NBVC** · 03-10-2011, 10:20 AM

For the second formula (countif) you can use:

=COUNTIFS(A1:A7,"*1*",A1:A7,"*2*")

or

=COUNTIFS(A1:A7,"*"&X1&"*",A1:A7,"*"&X2&"*")

if X1 and X2 contain the 2 lookup digits.

I don't get Part 1.. you have a result in C1 of 12.. is that it? If so, why?

**khank** · 03-10-2011, 10:53 AM

Originally Posted by NBVC

For the second formula (countif) you can use:

=COUNTIFS(A1:A7,"*1*",A1:A7,"*2*")

or

=COUNTIFS(A1:A7,"*"&X1&"*",A1:A7,"*"&X2&"*")

if X1 and X2 contain the 2 lookup digits.

I don't get Part 1.. you have a result in C1 of 12.. is that it? If so, why?

Sorry I didn't explain myself clearly. C1:12 is the 2-digit sequence I'm looking either 12 or 21.

I tried both without any luck. Both came up with "0". ???? Couldn't I use the "countifs" function with "12" and "21"?

**NBVC** · 03-10-2011, 10:56 AM

Originally Posted by khank

Couldn't I use the "countifs" function with "12" and "21"?

You could do it easily with:

=Countif(A1:A7,C1)+Countif(A1:A7,C2)

if you have C1 = 12 and C2 = 21

or if you want to have only C1 =12, then

=COUNTIFS(A1:A7,"*"&LEFT(C1)&"*",A1:A7,"*"&RIGHT(C1)&"*")