Formulas Returning Wrong Values

[CARBOB]

Will someone please tell me why the formulas in G:K are returning wrong values. They are suppose to count how many of the values in B:F repeat. I think it may be a format problem, but can't find it.

Book1
	A	B	C	D	E	F	G	H	I	J	K
14							1	2	3	4	5
15	12/30/16	03	14	17	25	36	1	1	1	1	1
16	12/29/16	03	05	13	23	36	1	1	1	1	1
17	12/28/16	07	14	22	34	35	1	1	1	1	1
18	12/27/16	05	09	13	15	17	1	1	1	1	1
19	12/26/16	13	26	30	33	36	1	1	1	1	1
LAST DIGIT (2)

 

[MARK858]

Oooh, nice trick - thanks for that!

Doesn't work here in Firefox: I just get an empty message box.

Try with the A/A icon (switch editor) highlighted

 

[Marcílio_Lobão]

CARBOB, Good afternoon.

Are you wanting to count how many times the digit 1 appears in the 5 numbers on the same line?

If so, try using this formula.

G15 --> =SUMPRODUCT(--((LEN($B15:$F15)-LEN(SUBSTITUTE($B15:$F15, G$14, ""))) / LEN(G$14)))

Copy it to the right and down.

Is that what you want?
I hope it helps.

 

[CARBOB]

thank you.

 

[CARBOB]

thank you.

Finally figured out how to post the code. I have Excel 2016 and it is a lot different than 2007.

sorry for all the confusion and the formula is suppose to be entered CSE. Thanks for all the help.

Book1
	A	B	C	D	E	F	G	H	I	J	K
14							1	2	3	4	5
15	12/30/16	03	14	17	25	36	2	3	4	4	4
16	12/29/16	03	05	13	23	36	0	2	3	4	4
17	12/28/16	07	14	22	34	35	0	0	0	1	1
18	12/27/16	05	09	13	15	17	1	1	1	2	2
19	12/26/16	13	26	30	33	36	0	1	2	2	3
LAST DIGIT (2)

Cell Formulas
Range	Formula
G15	{=SUMPRODUCT(IF(COUNTIF(OFFSET($B15,1,0,G$14,5),$B15:$F15)$B15:$F15,$B15:$F15),COUNTIF(OFFSET($B15,1,0,G$14,5),$B15:$F15)/COUNTIF($B15:$F15,$B15:$F15),1))}
G16	{=SUMPRODUCT(IF(COUNTIF(OFFSET($B16,1,0,G$14,5),$B16:$F16)$B16:$F16,$B16:$F16),COUNTIF(OFFSET($B16,1,0,G$14,5),$B16:$F16)/COUNTIF($B16:$F16,$B16:$F16),1))}
G17	{=SUMPRODUCT(IF(COUNTIF(OFFSET($B17,1,0,G$14,5),$B17:$F17)$B17:$F17,$B17:$F17),COUNTIF(OFFSET($B17,1,0,G$14,5),$B17:$F17)/COUNTIF($B17:$F17,$B17:$F17),1))}
G18	{=SUMPRODUCT(IF(COUNTIF(OFFSET($B18,1,0,G$14,5),$B18:$F18)$B18:$F18,$B18:$F18),COUNTIF(OFFSET($B18,1,0,G$14,5),$B18:$F18)/COUNTIF($B18:$F18,$B18:$F18),1))}
G19	{=SUMPRODUCT(IF(COUNTIF(OFFSET($B19,1,0,G$14,5),$B19:$F19)$B19:$F19,$B19:$F19),COUNTIF(OFFSET($B19,1,0,G$14,5),$B19:$F19)/COUNTIF($B19:$F19,$B19:$F19),1))}
H15	{=SUMPRODUCT(IF(COUNTIF(OFFSET($B15,1,0,H$14,5),$B15:$F15)$B15:$F15,$B15:$F15),COUNTIF(OFFSET($B15,1,0,H$14,5),$B15:$F15)/COUNTIF($B15:$F15,$B15:$F15),1))}
H16	{=SUMPRODUCT(IF(COUNTIF(OFFSET($B16,1,0,H$14,5),$B16:$F16)$B16:$F16,$B16:$F16),COUNTIF(OFFSET($B16,1,0,H$14,5),$B16:$F16)/COUNTIF($B16:$F16,$B16:$F16),1))}
H17	{=SUMPRODUCT(IF(COUNTIF(OFFSET($B17,1,0,H$14,5),$B17:$F17)$B17:$F17,$B17:$F17),COUNTIF(OFFSET($B17,1,0,H$14,5),$B17:$F17)/COUNTIF($B17:$F17,$B17:$F17),1))}
H18	{=SUMPRODUCT(IF(COUNTIF(OFFSET($B18,1,0,H$14,5),$B18:$F18)$B18:$F18,$B18:$F18),COUNTIF(OFFSET($B18,1,0,H$14,5),$B18:$F18)/COUNTIF($B18:$F18,$B18:$F18),1))}
H19	{=SUMPRODUCT(IF(COUNTIF(OFFSET($B19,1,0,H$14,5),$B19:$F19)$B19:$F19,$B19:$F19),COUNTIF(OFFSET($B19,1,0,H$14,5),$B19:$F19)/COUNTIF($B19:$F19,$B19:$F19),1))}
I15	{=SUMPRODUCT(IF(COUNTIF(OFFSET($B15,1,0,I$14,5),$B15:$F15)$B15:$F15,$B15:$F15),COUNTIF(OFFSET($B15,1,0,I$14,5),$B15:$F15)/COUNTIF($B15:$F15,$B15:$F15),1))}
I16	{=SUMPRODUCT(IF(COUNTIF(OFFSET($B16,1,0,I$14,5),$B16:$F16)$B16:$F16,$B16:$F16),COUNTIF(OFFSET($B16,1,0,I$14,5),$B16:$F16)/COUNTIF($B16:$F16,$B16:$F16),1))}
I17	{=SUMPRODUCT(IF(COUNTIF(OFFSET($B17,1,0,I$14,5),$B17:$F17)$B17:$F17,$B17:$F17),COUNTIF(OFFSET($B17,1,0,I$14,5),$B17:$F17)/COUNTIF($B17:$F17,$B17:$F17),1))}
I18	{=SUMPRODUCT(IF(COUNTIF(OFFSET($B18,1,0,I$14,5),$B18:$F18)$B18:$F18,$B18:$F18),COUNTIF(OFFSET($B18,1,0,I$14,5),$B18:$F18)/COUNTIF($B18:$F18,$B18:$F18),1))}
I19	{=SUMPRODUCT(IF(COUNTIF(OFFSET($B19,1,0,I$14,5),$B19:$F19)$B19:$F19,$B19:$F19),COUNTIF(OFFSET($B19,1,0,I$14,5),$B19:$F19)/COUNTIF($B19:$F19,$B19:$F19),1))}
J15	{=SUMPRODUCT(IF(COUNTIF(OFFSET($B15,1,0,J$14,5),$B15:$F15)$B15:$F15,$B15:$F15),COUNTIF(OFFSET($B15,1,0,J$14,5),$B15:$F15)/COUNTIF($B15:$F15,$B15:$F15),1))}
J16	{=SUMPRODUCT(IF(COUNTIF(OFFSET($B16,1,0,J$14,5),$B16:$F16)$B16:$F16,$B16:$F16),COUNTIF(OFFSET($B16,1,0,J$14,5),$B16:$F16)/COUNTIF($B16:$F16,$B16:$F16),1))}
J17	{=SUMPRODUCT(IF(COUNTIF(OFFSET($B17,1,0,J$14,5),$B17:$F17)$B17:$F17,$B17:$F17),COUNTIF(OFFSET($B17,1,0,J$14,5),$B17:$F17)/COUNTIF($B17:$F17,$B17:$F17),1))}
J18	{=SUMPRODUCT(IF(COUNTIF(OFFSET($B18,1,0,J$14,5),$B18:$F18)$B18:$F18,$B18:$F18),COUNTIF(OFFSET($B18,1,0,J$14,5),$B18:$F18)/COUNTIF($B18:$F18,$B18:$F18),1))}
J19	{=SUMPRODUCT(IF(COUNTIF(OFFSET($B19,1,0,J$14,5),$B19:$F19)$B19:$F19,$B19:$F19),COUNTIF(OFFSET($B19,1,0,J$14,5),$B19:$F19)/COUNTIF($B19:$F19,$B19:$F19),1))}
K15	{=SUMPRODUCT(IF(COUNTIF(OFFSET($B15,1,0,K$14,5),$B15:$F15)$B15:$F15,$B15:$F15),COUNTIF(OFFSET($B15,1,0,K$14,5),$B15:$F15)/COUNTIF($B15:$F15,$B15:$F15),1))}
K16	{=SUMPRODUCT(IF(COUNTIF(OFFSET($B16,1,0,K$14,5),$B16:$F16)$B16:$F16,$B16:$F16),COUNTIF(OFFSET($B16,1,0,K$14,5),$B16:$F16)/COUNTIF($B16:$F16,$B16:$F16),1))}
K17	{=SUMPRODUCT(IF(COUNTIF(OFFSET($B17,1,0,K$14,5),$B17:$F17)$B17:$F17,$B17:$F17),COUNTIF(OFFSET($B17,1,0,K$14,5),$B17:$F17)/COUNTIF($B17:$F17,$B17:$F17),1))}
K18	{=SUMPRODUCT(IF(COUNTIF(OFFSET($B18,1,0,K$14,5),$B18:$F18)$B18:$F18,$B18:$F18),COUNTIF(OFFSET($B18,1,0,K$14,5),$B18:$F18)/COUNTIF($B18:$F18,$B18:$F18),1))}
K19	{=SUMPRODUCT(IF(COUNTIF(OFFSET($B19,1,0,K$14,5),$B19:$F19)$B19:$F19,$B19:$F19),COUNTIF(OFFSET($B19,1,0,K$14,5),$B19:$F19)/COUNTIF($B19:$F19,$B19:$F19),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.