COUNTIF formula help!

[keranali]

How do you count the number of rows only if there is a number in a specific cell e.g

Please ignore the highlights on the cells

Ying 1.4.2.xlsm
	AD
3	Play Count
4	5
Overview

Cell Formulas
Range	Formula
AD4	AD4	=COUNT(AM52:AM5000)

Ying 1.4.2.xlsm
	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ
52	9	15	26	1	2	25	29	7	16	8	12	17	24	28	31	22	3	4	5	13	20	36		33
53	9	15	26	1	2	25	29	7	16	8	12	17	24	28	31	22	3	4	5	13	20	36		17
54	9	15	26	1	2	25	29	7	16	8	12	17	24	28	31	22	3	4	5	13	20	36		5
55	9	15	26	1	2	25	29	7	16	8	12	17	24	28	31	22	3	4	5	13	20	36		6
56	9	23	26	1	2	25	29	7	16	8	12	24	28	31	17	22	3	4	13	30	11	36
Overview

Once you enter a number on BJ56 it will increase the count to 5 lines on AD4 and remain 4 count if nothing is entered
Also instead of using AM52:AM5000 is there a way to not use 5000 to like AM:AM but starting from row 56

 

[DanteAmor]

Hi Keranale. Thanks for posting on the forum.

Also instead of using AM52:AM5000 is there a way to not use 5000 to like AM:AM but starting from row 56

I didn't understand if you want to start at 52 or 56, but try the following to start at 52:

Dante Amor
	AD
3	Play Count
4	4
Hoja18

Cell Formulas
Range	Formula
AD4	AD4	=COUNTIFS(AM:AM,"<>",BJ:BJ,"<>")-COUNT(AM1:AM51)

varios 05may2023.xlsm
	AL	AM	AN	AO																					BJ
52		9																							1
53		9																							1
54		9																							1
55		9																							1
56		9
57
Hoja18

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

 

[DanteAmor]

There is something I didn't consider, please try the following formula:

Dante Amor
	AD
3	Play Count
4	4
Hoja18

Cell Formulas
Range	Formula
AD4	AD4	=SUMPRODUCT((AM:AM<>"")*(BJ:BJ<>"")*(ROW(AM:AM)>51))

varios 05may2023.xlsm
	AL	AM	AN	AO																					BJ
51		9																							1
52		9																							1
53		9																							1
54		9																							1
55		9																							1
56		9
57
Hoja18

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

 

[Peter_SSs]

Also instead of using AM52:AM5000 is there a way to not use 5000 to like AM:AM but starting from row ..

I would strongly recommend continuing to use a numbered range. Unless you have something like a million rows of actual data, you may be asking Excel to do an enormous amount of extra calculations for nothing, especially if using some functions like SUMPRODUCT.
Where you have 5000 just use a number that will be plenty big enough to cover any data you have. for example, if you used, say, 10,000 that would require calculating less than 1% of the available rows.

This would be my suggestion.

=COUNT(FILTER(AM52:AM10000,BJ52:BJ10000<>"",""))

 

[DanteAmor]

You're right Peter, sumproduct will affect the performance of the sheet, so if you'll excuse me, I'll go back to the formula in post #2.

Dante Amor
	AD
3	Play Count
4	4
Hoja18

Cell Formulas
Range	Formula
AD4	AD4	=COUNTIFS(AM52:AM5000,"<>",BJ52:BJ5000,"<>")

Dante Amor
	AL	AM	AN	AO																					BJ	BK	BL	BM
51		9																							1
52		9																							1
53		9																							1
54		9																							1
55		9																							1
56		9
57
Hoja18

 

[keranali]

I would strongly recommend continuing to use a numbered range. Unless you have something like a million rows of actual data, you may be asking Excel to do an enormous amount of extra calculations for nothing, especially if using some functions like SUMPRODUCT.
Where you have 5000 just use a number that will be plenty big enough to cover any data you have. for example, if you used, say, 10,000 that would require calculating less than 1% of the available rows.

This would be my suggestion.

=COUNT(FILTER(AM52:AM10000,BJ52:BJ10000<>"",""))

Thanks for the tip on the cells range and the formula It worked well

 

[keranali]

Thanks Guys

 

[Peter_SSs]

You're welcome. Glad to help.