Can LEFT, MID, or RIGHT be used with a COUNTIF or VLOOKUP
 

There are single digits in Col A (0-9). I want to know how many times the
double digit numbers in Col B end in the number from Col A. (e.g. if Col
A=1then if Col B=11, 21, 31 etc a counter would increment.)

Can LEFT, MID, or RIGHT be used with a COUNTIF or VLOOKUP
 

The last digit in B is readily found with MOD; so if B1 =21 then =MOD(B1,10)
will return the remainder of dividing 21 by 10 (ie 1)
This counts how many times the digit in A equals the last digit in B
=SUMPRODUCT(--(A1:A8=MOD(B1:B8,10)))

This will return how may times cells in A =1 and cell in B end with 1
=SUMPRODUCT(--(A1:A8)=1),--(A1:A8=MOD(B1:B8,10)))

Only in Excel 2007 can you use full cell references as in
=SUMPRODUCT(--(A:A=MOD(B:B,10)))

best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"PJ Murph" wrote in message
...
There are single digits in Col A (0-9). I want to know how many times the
double digit numbers in Col B end in the number from Col A. (e.g. if Col
A=1then if Col B=11, 21, 31 etc a counter would increment.)

Can LEFT, MID, or RIGHT be used with a COUNTIF or VLOOKUP
 

Hi,

Here is another answer:

=SUMPRODUCT(--(--RIGHT(B$1:B$9)=A1))

and I may be misreading your question but i think

=SUMPRODUCT(--(A1=MOD(B$1:B$9,10)))

may be what you want using Bernard's suggestion.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"PJ Murph" wrote:

There are single digits in Col A (0-9). I want to know how many times the
double digit numbers in Col B end in the number from Col A. (e.g. if Col
A=1then if Col B=11, 21, 31 etc a counter would increment.)

Can LEFT, MID, or RIGHT be used with a COUNTIF or VLOOKUP
 

Thanks Bernard, the formula is awesome.

"Bernard Liengme" wrote:

The last digit in B is readily found with MOD; so if B1 =21 then =MOD(B1,10)
will return the remainder of dividing 21 by 10 (ie 1)
This counts how many times the digit in A equals the last digit in B
=SUMPRODUCT(--(A1:A8=MOD(B1:B8,10)))

This will return how may times cells in A =1 and cell in B end with 1
=SUMPRODUCT(--(A1:A8)=1),--(A1:A8=MOD(B1:B8,10)))

Only in Excel 2007 can you use full cell references as in
=SUMPRODUCT(--(A:A=MOD(B:B,10)))

best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"PJ Murph" wrote in message
...
There are single digits in Col A (0-9). I want to know how many times the
double digit numbers in Col B end in the number from Col A. (e.g. if Col
A=1then if Col B=11, 21, 31 etc a counter would increment.)