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

  • Thread starter Thread starter PJ Murph
  • Start date Start date
P

PJ Murph

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.)
 
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 Liengme said:
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)
Click to expand...
....

Does depend on the magnitude of the 1st argument to MOD, e.g., =MOD
(2^31,10) returns #NUM! rather than 8. As long as the OP's col B
values are less than 1,000,000,000, not an issue.
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)))
Click to expand...
....

Note typo - the formula above has more right parentheses than left
parentheses.

More efficient to skip the second A1:A8 reference, so

=SUMPRODUCT(--(A1:A8=x),--(MOD(B1:B8,10)=x))
 
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.
 
Thanks Bernard, the formula is awesome.

Bernard Liengme said:
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
Click to expand...
 
Back
Top