If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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.) |
#2
|
|||
|
|||
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.) |
#3
|
|||
|
|||
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.) |
#4
|
|||
|
|||
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.) |
Thread Tools | |
Display Modes | |
|
|