Count as 1

[deemer98]

In the range of cells I8:R8, I11:R11, I14:R14, I17:R17,I20:R20

I want cell J25 to count as "1", any cell that has a number in that range except "0".

Thanks

 

[Scott Huish]

=sumproduct((mod(row(i8:i20)-row(i8),3)=0)*isnumber(i8:r20)*(i8:r20<>0))

 

[dreid1011]

This is a duplicate post, you have responses in the previous thread to which you responded as well.

https://www.mrexcel.com/forum/excel-questions/1024580-amateur-excel-guy-struggling.html

 

[Scott Huish]

I put the answer he needed here, so I will lock the original post.

 

[deemer98]

That just gave me a "0" on my cell

 

[Scott Huish]

It shouldn't have:

Excel 2010
	I	J	K	L	M	N	O	P	Q	R
8			0		0
9
10
11
12
13
14			17
15
16
17
18
19
20			5		7
21
22
23
24	3
Sheet1

Cell Formulas
Range	Formula
I24	=SUMPRODUCT((MOD(ROW(I8:I20)-ROW(I8),3)=0)*ISNUMBER(I8:R20)*(I8:R20<>0))

 

[Greg Truby]

That just gave me a "0" on my cell

Just confirming for Scott. Your formula is working just fine for me.

 

[dreid1011]

Just confirming for Scott. Your formula is working just fine for me.

Then perhaps his numbers are not really numbers.

 

[Scott Huish]

=sumproduct((mod(row(i8:i20)-row(i8),3)=0)*isnumber(--i8:r20)*(i8:r20<>0))

 

[deemer98]

Yes, I reposted in order to try to make it more clear. I think I may have not had one row accounted for in my sheet by I have it working now. And yes, I really do have numbers in the cells. But instead of picking every other line (the only ones with #'s), I just used the entire range (w/letters and #'s) since I was only looking for anything greater than "0". therefore =COUNTIF(I8:R21,">0") worked great. Thanks for the feedback guys!