How to do an operation on a range inside of a formula

[tagout]

Is there a way to perform an operation on a range as part of another formula? I have a column of text. I want to search that column to see four characters match the last four characters of any cell in the range. So, my formula would look something like this: =COUNTIF( RIGHT(A1:A8, 4), "ABCD") This gives me an error.

Now, I know that I can create another column and use the RIGHT formula to get the last 4 characters and then search that column. However, I am wondering if there is a way to do the search without creating a new column.

Thanks for any help,

 

[jtakw]

Hi,

Use this:

=COUNTIF(A1:A8,"*ABCD")

 

[tagout]

Hi,

Use this:

=COUNTIF(A1:A8,"*ABCD")

Hi Jtakw
That is a good answer, but I should have complicated my question a bit more. What if what I want check is the last four characters of another text value.

=COUNTIF(LEFT(A1:A8,4), LEFT(B1,4))

 

[jtakw]

Is it Left or Right, you switched.

Book3.xlsx
	A	B	C
1		2	abcdxyz
2
3	abcdtext
4
5	abctext
6	abcdabc
7
8
Sheet913

Cell Formulas
Range	Formula
B1	B1	=COUNTIF(A1:A8,LEFT(C1,4)&"*")

 

[tagout]

Is it Left or Right, you switched.

Book3.xlsx
	A	B	C
1		2	abcdxyz
2
3	abcdtext
4
5	abctext
6	abcdabc
7
8
Sheet913

Cell Formulas
Range	Formula
B1	B1	=COUNTIF(A1:A8,LEFT(C1,4)&"*")

Mr. jtakw,
Thank you. That is exactly what I need. You 'da man.

 

[jtakw]

You're welcome, thanks for the feedback.

Don't forget to mark the post that solved your question as solution. Thanks.

 

[tagout]

Mr. jtakw,
Thank you. That is exactly what I need. You 'da man.

Sorry, I did switch. I want right but kept thinking left. Still, I was able to figure out what you were doing and make it work for right.

 

[jtakw]

Sorry, I did switch. I want right but kept thinking left. Still, I was able to figure out what you were doing and make it work for right.

Just in case:

Book3.xlsx
	A	B	C
1		2	xyzabcd
2
3	textabcd
4
5	textabc
6	abcabcd
Sheet913

Cell Formulas
Range	Formula
B1	B1	=COUNTIF(A1:A8,"*"&RIGHT(C1,4))