Count a value in a cell (within a specified range) every nth cell along the row

[nature98]

I am stumped. I tried researching the forums and google to get a formula that work. I am trying to count a value in a cell (with a range of 3 or higher) every 4th cell along the row. I do not want the formula to count text or blank cells. My cell range across the row is G28:HK28. The cell I want to count starts at G28. Hope you gurus can help. I know how to use the CountIF functions but when I tried the sumproduct and mod functions, I just cannot get it to add up correctly.

Help me Okbi Wan Kenobi...you are my only hope.

 

[KRice]

If G28 is the 1st cell to consider, what is the next?...I'm not clear on whether every 4th cell means J28 or K28.

 

[nature98]

If G28 is the 1st cell to consider, what is the next?...I'm not clear on whether every 4th cell means J28 or K28.

You are correct. J28 would be the next cell to count.

 

[KRice]

Thanks...and when you say:

I do not want the formula to count text or blank cells.

...could we say that you only want to count the cells if the contents are numbers...including 0? There is a nuance in Excel regarding the definition of a "blank" cell, so I'd like to get some clarity on this point.

 

[KRice]

Have a look to see if this delivers expected results:

Book1
	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO
28		3	4	2	1	4	5	2	6	7	1	9	4	6	7	4	5	4	5	6	8	2	1	1	1	4	6		a			-1			-9
29	count	5
Sheet2

Cell Formulas
Range	Formula
G29	G29	=LET(a,FILTER(G28:HK28,MOD(SEQUENCE(,COLUMNS(G28:HK28),0),3)=0),SUM((a>=3)*ISNUMBER(a)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G28:HK28	Expression	=MOD(COLUMNS($G$28:G$28)-1,3)=0	text	NO

 

[nature98]

Thanks...and when you say:

...could we say that you only want to count the cells if the contents are numbers...including 0? There is a nuance in Excel regarding the definition of a "blank" cell, so I'd like to get some clarity on this point.

There is no zero in the cell. It will be blank. I have a pick list in the cells I wish to count. When a value is not picked, it is blank. The range of the pick list in a cell is "X, 1, 2 , 3 ,4".

 

[nature98]

Have a look to see if this delivers expected results:

Book1
	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO
28		3	4	2	1	4	5	2	6	7	1	9	4	6	7	4	5	4	5	6	8	2	1	1	1	4	6		a			-1			-9
29	count	5
Sheet2

Cell Formulas
Range	Formula
G29	G29	=LET(a,FILTER(G28:HK28,MOD(SEQUENCE(,COLUMNS(G28:HK28),0),3)=0),SUM((a>=3)*ISNUMBER(a)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G28:HK28	Expression	=MOD(COLUMNS($G$28:G$28)-1,3)=0	text	NO

Brilliant! Your formula worked! Thank you so much!

 

[nature98]

Brilliant! Your formula worked! Thank you so much!

One last question. If I wanted to count the letter "X" in my range of values within the cell to count what wopuld the formula look like?

 

[KRice]

I think the earlier solution could be simplified...the a>=3 condition automatically means the value must be a number, so the ISNUMBER part isn't needed. Here's a revision to accommodate "X":

MrExcel_20240203.xlsx
	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO
28		3	4	2	1	4	5	2	6	7	1	9	4	6	7	4	5	4	5	6	8	2	2	1	1	5	6		a			X			-9
29	count	7
Sheet2

Cell Formulas
Range	Formula
G29	G29	=LET(a,FILTER(G28:HK28,MOD(SEQUENCE(,COLUMNS(G28:HK28),0),3)=0),SUM(--(((a="X")+(a>=3))>0)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G28:HK28	Expression	=MOD(COLUMNS($G$28:G$28)-1,3)=0	text	NO

 

[KRice]

Sorry...just occurred to me that text will evaluate as >3, so there is a problem with last post. This should resolve the problem:

MrExcel_20240203.xlsx
	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO
28		3	4	2	1	4	5	2	6	7	1	9	4	6	7	4	5	4	5	6	8	2	2	1	1	5	6		a			X			-9
29	count	6
Sheet2

Cell Formulas
Range	Formula
G29	G29	=LET(a,FILTER(G28:HK28,MOD(SEQUENCE(,COLUMNS(G28:HK28),0),3)=0),SUM(--(((a="X")+ISNUMBER(a)*(a>=3))>0)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G28:HK28	Expression	=MOD(COLUMNS($G$28:G$28)-1,3)=0	text	NO