How can I; COUNTIF(C4:G4,"compare last digit only of values in range to Criteria cell)

[Poker Joe]

I'd like to enter a formula in I9 and drag it across to R9, go through and change the references and drag it down 650 rows.
A macro would be nice but I'll take a formula if I can get the right functions to give me what I need.

I've tried several formulas to no avail.

=COUNTIF(C4:G4,"*"&I9)
=COUNTIF(C4:G4,"*"&TEXT(I9,"0"))
=COUNTIF(C4:G4,"*"&TEXT(I9,"0")&"")
=COUNTIF(C4:G4,"*"&TEXT(I9,"0")&"$")

											Last Digit Reference
ID	Date	M	T	W	Th	F		0	1	2	3	4	5	6	7	8	9
1	04/25/2021	da9	kl16	mb8	ys6	ql3					1			2		1	1
2	04/26/2021	ab2	ab3	ave5	mb12	ql3				2	2		1
3	04/27/2021	ve9	pl5	th5	mb12	ys4				1		1	2				1
4	04/28/2021	ab2	ve7	pl6	mb10	ql3
5	04/29/2021	da8	kl16	pl4	mb8	ys5
6	04/30/2021	ab2	ve9	da7	pl6	pl7
7	05/01/2021	ab2	ve8	th6	th9	ys4
8	05/02/2021	ve7	ve8	da8	th5	ys6
9	05/03/2021	ab4	ab5	th6	mb10	ql3
10	05/04/2021	ab4	kl12	th9	ys5	ys6
11	05/05/2021	da9	kl14	kl15	pl3	pl5
12	05/06/2021	ab4	pl4	pl6	mb10	ys7
13	05/07/2021	da9	th6	th9	mb10	ql4

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2												Last Digit Reference
3	ID	Date	M	T	W	Th	F		0	1	2	3	4	5	6	7	8	9
4	1	44311	da9	kl16	mb8	ys6	ql3		0	0	0	1	0	0	2	0	1	1
5	2	44312	ab2	ab3	ave5	mb12	ql3		0	0	2	2	0	1	0	0	0	0
6	3	44313	ve9	pl5	th5	mb12	ys4		0	0	1	0	1	2	0	0	0	1
7	4	44314	ab2	ve7	pl6	mb10	ql3
8	5	44315	da8	kl16	pl4	mb8	ys5
9	6	44316	ab2	ve9	da7	pl6	pl7
10	7	44317	ab2	ve8	th6	th9	ys4
11	8	44318	ve7	ve8	da8	th5	ys6
12	9	44319	ab4	ab5	th6	mb10	ql3
13	10	44320	ab4	kl12	th9	ys5	ys6
14	11	44321	da9	kl14	kl15	pl3	pl5
15	12	44322	ab4	pl4	pl6	mb10	ys7
16	13	44323	da9	th6	th9	mb10	ql4
Main

Cell Formulas
Range	Formula
I4:R6	I4	=COUNTIF($C4:$G4,"*"&I$3)

 

[Poker Joe]

That works fine on my XLBB worksheet but I have another workbook with a similar worksheet and the formula doesn't work on that one.
The format of all the cells is General. Now, some of the cells have values generated by a macro and these cells are referenced in the range part of the formula.

That formula works but for some reason in the workbook/worksheet I want to use it in, it returns a value of zero for cells that have the same last digit value as the criteria cell in the CountIf function.

 

[Fluff]

Are all the values text strings, or are they numbers?

 

[Poker Joe]

Are all the values text strings, or are they numbers?

They are numbers.

 

[Fluff]

That's why it doesn't work. Using wildcards only works with text.
If your values are numbers why did you post data that is text?

 

[Fluff]

How about

=SUMPRODUCT(--(RIGHT($C4:$G4,1)=I$3&""))

 

[Poker Joe]

How about

=SUMPRODUCT(--(RIGHT($C4:$G4,1)=I$3&""))

Hmmm...sorry, I didn't realize it made a difference. I already had that XLBB spreadsheet open using it for another project question, so I used it. I'm having trouble utilizing XLBB in the workbook I'm currently working in. I spend more time troubleshooting XLBB than working in the workbook.

The SUMPRODUCT formula works great! I appreciate it and I will duly note TEXT and NUMBERS make a difference. I knew they did for some things but wasn't aware for this question. Now, I am. Thanks!

 

[Fluff]

Glad to help & thanks for the feedback.

 

[Poker Joe]

Thank You.