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.

 

[nature98]

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

The formula works. Thank you soooooo much! Really appreciate your help with the excel formula.

 

[KRice]

You're welcome...glad to help, and welcome to the MrExcel board.

 

[nature98]

You're welcome...glad to help, and welcome to the MrExcel board.

One last question for you KRice. The same question above applies and your table still apllies...but this time, what is the formula to SUM the values every 4th cell along the row.

 

[KRice]

Do you want to ignore any "X" values, or assign them some value?

The easiest option is to adapt the earlier formula and swap out SUM for SUMPRODUCT and incorporate the "a" array (the filtered array containing array positions 1, 4, 7, etc.), but ignoring the "X"...it would look like this:

MrExcel_20240203.xlsx
	F	G
30	sum	25
Sheet2

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

 

[nature98]

Do you want to ignore any "X" values, or assign them some value?

The easiest option is to adapt the earlier formula and swap out SUM for SUMPRODUCT and incorporate the "a" array (the filtered array containing array positions 1, 4, 7, etc.), but ignoring the "X"...it would look like this:

MrExcel_20240203.xlsx
	F	G
30	sum	25
Sheet2

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

X would be ignored. But if I nneed to count X...include that fornula as well.

 

[KRice]

Okay...so the counting formula uses SUM in post #10, and the summing formula uses SUMPRODUCT in post #14 (and it just ignores "X" values").

 

[nature98]

Okay...so the counting formula uses SUM in post #10, and the summing formula uses SUMPRODUCT in post #14 (and it just ignores "X" values").

Got it. Another question popped up LOL! How do I calculate the COUNT of only "X"?

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

Remove the text in red?

 

[KRice]

Almost. The addition is used to establish an OR condition...any value in the "a" array may be either =X or >=3 or (not in this case), both. This leads to four combinations of TRUE's and FALSE's, and when those are added, TRUE is coerced to 1, and FALSE to 0. So the four combinations are 0+0=0, 1+0=1, 0+1=1, 1+1=2, and the >0 condition is used to accept any items satisfying at least one condition. But if you want to count only the X's, then we can simply evaluate the "a" array for X's, coerce those TRUE's and FALSE's to 1's and 0's using the double unary operator (--), and sum, as shown below (note that both "x" and "X" are accepted here):

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	AP	AQ
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			x
29	count	7
30	sum	25
31	count X	2
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)))
G30	G30	=LET(a,FILTER(G28:HK28,MOD(SEQUENCE(,COLUMNS(G28:HK28),0),3)=0),SUMPRODUCT(a,ISNUMBER(a)*(a>=3)))
G31	G31	=LET(a,FILTER(G28:HK28,MOD(SEQUENCE(,COLUMNS(G28:HK28),0),3)=0),SUM(--(a="X")))

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