COUNTIF formula help!

keranali

Rules Violation
Joined
Oct 4, 2010
Messages
234
Office Version
  1. 365
Platform
  1. Windows
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
3Play Count
45
Overview
Cell Formulas
RangeFormula
AD4AD4=COUNT(AM52:AM5000)


Ying 1.4.2.xlsm
AMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJ
5291526122529716812172428312234513203633
5391526122529716812172428312234513203617
549152612252971681217242831223451320365
559152612252971681217242831223451320366
569232612252971681224283117223413301136
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
 
Last edited:

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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
3Play Count
44
Hoja18
Cell Formulas
RangeFormula
AD4AD4=COUNTIFS(AM:AM,"<>",BJ:BJ,"<>")-COUNT(AM1:AM51)

varios 05may2023.xlsm
ALAMANAOBJ
5291
5391
5491
5591
569
57
Hoja18



--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------
 
Upvote 1
There is something I didn't consider, please try the following formula:
Dante Amor
AD
3Play Count
44
Hoja18
Cell Formulas
RangeFormula
AD4AD4=SUMPRODUCT((AM:AM<>"")*(BJ:BJ<>"")*(ROW(AM:AM)>51))

varios 05may2023.xlsm
ALAMANAOBJ
5191
5291
5391
5491
5591
569
57
Hoja18


--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------
 
Upvote 1
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.
Excel Formula:
=COUNT(FILTER(AM52:AM10000,BJ52:BJ10000<>"",""))
 
Upvote 1
Solution
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
3Play Count
44
Hoja18
Cell Formulas
RangeFormula
AD4AD4=COUNTIFS(AM52:AM5000,"<>",BJ52:BJ5000,"<>")

Dante Amor
ALAMANAOBJBKBLBM
5191
5291
5391
5491
5591
569
57
Hoja18


😬
 
Upvote 0
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.
Excel Formula:
=COUNT(FILTER(AM52:AM10000,BJ52:BJ10000<>"",""))
Thanks for the tip on the cells range and the formula It worked well
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,189
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top