Saltar al contenido
PROGRAMAR EN VBA MACROS DE EXCEL

Como Contar Datos Con Una Condicion en Una Columna con CountIf


(adsbygoogle = window.adsbygoogle || []).push({});

En este poste se presenta una sencilla, pero útil macro que permite contar datos en Excel dependiendo de una condición, se apela a WorksheetFunction, que son las funciones de Excel que se pueden usar en macros de VBA, también se presentó otros ejemplos relacionados: como contar datos que cumplan con varias condiciones, como contar celdas vacías, como contar cualquier tipo de dato de un rango, como contar valores en un rango.

Desde el final del post se puede descargar el ejemplo en forma gratuita sin ninguna restricción, el código se puede adaptar a cada necesidad, Aporta por favor para sostener el sitio si está dentro de tus posibilidades, desde ya muchas gracias.

Si te estás iniciando en la operación de Excel o requieres afirmar conocimientos, recomiendo leer un excelente libro sobre Excel que te ayudará operar las planillas u hojas de cálculo, haz click acá, si quieres aprender sobre Excel, en inglés, entonces debes hacer click here. Si lo que necesitas es aprender o profundizar sobre la programación de macros con VBA este es unos de los mejores cursos on line que he visto en internet.

  
El vídeo verás la macro en acción con una explicación más detallada de su codificación y funcionamiento, recomiendo observar para una más fácil comprensión de la macro; suscribe a nuestra web desde la parte superior derecha de la página ingresando tu mail y a nuestro canal de You Tube para recibir en tu correo vídeos explicativos sobre macros interesantes, como  por ejemplo Recorre fila buscando y comparando datos de dos columnas en hojas distintasbuscar en listbox mientras escribes en textbox, como crear una factura o sale invoice y grabar guardar PDF XLS y enviar por mail, trabajando con filas, celdas, columnas, rangos y muchos ejemplos más.

function onYtEvent(payload) {
if (payload.eventType == ‘subscribe’) {
// Add code to handle subscribe event.
} else if (payload.eventType == ‘unsubscribe’) {
// Add code to handle unsubscribe event.
}
if (window.console) { // for debugging only
window.console.log(‘YT event: ‘, payload);
}
}

  

Al descargar el archivo se observa un botón que permite ejecutar una macro que cuenta todos los valores que cumplen una cierta condición y escribe el resultado en la celda siguiente a la última fila con datos.

En este ejemplo se determina cual es la ultima fila con datos y en ese rango se procede a contar los valores que cumplan la condición de ser mayores a 100.000 (cien mil); si el valor de la celda cumple esa condición es contado por la macro, escribiendo en la celda A25 el resultado de la cuenta realizada.


⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛

Quizá sea de utilidad también

Como llenar combobox y buscar datos

Cronometro en VBA para Excel

Formulario de VBA para insertar datos

⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛

⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛

Para contar se utiliza WorksheetFunctión, que como ya se explicó en post anteriores permite usar todas o mejor dicho casi todas, porque algunas funciones no se pueden usar, en macros programadas con VBA (Visual Basic Aplication), en código que permite contar datos con una condición en una columna de Excel es:

Application.WorksheetFunction.CountIf(Range(«A2» & «:A» & uf – 1), «>100000»)

Como se observa en el código anterior es necesario determinar el rango donde la función debe realizar la cuenta y como segundo item el criterio que debe tener en cuenta, en este caso > 100000, a continuación se muestra el código completo.

Código que se inserta en un módulo

Sub CuentaIf()
Application.ScreenUpdating = False
On Error Resume Next
Dim uf As String
uf = Sheets(«Hoja1»).Range(«A» & Rows.Count).End(xlUp).Row
Cells(uf, «A»).ClearContents
Cells(uf, «A») = Application.WorksheetFunction.CountIf(Range(«A2» & «:A» & uf – 1), «>100000»)
MsgBox («La cantidad de registros es: » & Cells(uf, «A»)), vbInformation, «AVISO»
DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Si te fue de utilidad puedes INVITARME UN CAFÉ y de esta manera ayudar a seguir manteniendo la página, CLICK para descargar en ejemplo en forma gratuita.


(adsbygoogle = window.adsbygoogle || []).push({});

If this post was helpful INVITE ME A COFFEE and so help keep up the page, CLICK to download free example.

Si te gustó por favor compártelo con tus amigos
If you liked please share it with your friends      

Entradas relacionadas

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Comentarios (38)

Hey! I know this is sort of off-topic but I
needed to ask. Does running a well-established blog
like yours take a large amount of work? I am brand new to
operating a blog but I do write in my journal on a daily
basis. I’d like to start a blog so I will be able to share
my personal experience and thoughts online. Please let
me know if you have any kind of ideas or tips for new aspiring blog owners.
Appreciate it!

Responder

Excellent post however I was wondering if you could write a litte more on this subject?
I’d be very grateful if you could elaborate a little bit more.
Cheers!

Responder

Thanks for a marvelous posting! I actually enjoyed reading it, you will be
a great author.I will ensure that I bookmark your blog and will eventually
come back later on. I want to encourage that you continue your
great posts, have a nice afternoon!

Responder

Thank you for the auspicious writeup. It in fact was a amusement account it.
Look advanced to more added agreeable from you! By the way, how could we communicate?

Responder

Right now it looks like WordPress is the preferred blogging
platform out there right now. (from what I’ve read) Is that what you’re using on your blog?

Responder

You actually make it seem so easy with your presentation but I find this
matter to be actually something which I think I would never understand.

It seems too complicated and very broad for me. I’m looking forward for
your next post, I will try to get the hang of it!

Responder

When someone writes an paragraph he/she keeps the thought of a user in his/her brain that how a user can be aware of it.
Therefore that’s why this article is amazing.

Thanks!

Responder

Today, I went to the beach with my children. I found a sea shell and
gave it to my 4 year old daughter and said «You can hear the ocean if you put this to your ear.» She placed the shell to her
ear and screamed. There was a hermit crab inside and it pinched her ear.
She never wants to go back! LoL I know this is entirely off topic but I had to tell someone!

Responder

Excellent, what a blog it is! This website gives helpful data to
us, keep it up.

Responder

Excellent blog here! Also your web site loads up fast!
What web host are you using? Can I get your
affiliate link to your host? I wish my website loaded up as fast as yours lol

Responder

My partner and I stumbled over here different web page and thought I
may as well check things out. I like what I see so now i’m following you.
Look forward to finding out about your web page
again.

Responder

Hi everyone, it’s my first pay a visit at this website, and article is genuinely fruitful in support of me, keep
up posting these posts.

Responder

Having read this I believed it was extremely informative.
I appreciate you spending some time and effort to put this short article together.
I once again find myself personally spending a lot of time both
reading and commenting. But so what, it was still worth it!

Responder

Hi! This is my first visit to your blog! We are a group of volunteers and starting
a new project in a community in the same niche. Your blog provided us valuable information to work on. You have done a wonderful job!

Responder

Touche. Great arguments. Keep up the good spirit.

Responder

Hello, I read your blogs regularly. Your humoristic style is awesome, keep up the good work!

Responder

I love what you guys tend to be up too. This sort of clever work and exposure!

Keep up the good works guys I’ve added you guys to my
personal blogroll.

Responder

Nice answers in return of this question with solid arguments and telling all
regarding that.

Responder

What’s Happening i’m new to this, I stumbled upon this I have found It absolutely useful and
it has helped me out loads. I hope to contribute & help other users
like its helped me. Great job.

Responder

I am really loving the theme/design of your site.
Do you ever run into any web browser compatibility problems?
A couple of my blog visitors have complained about my website not operating correctly in Explorer but looks great
in Chrome. Do you have any tips to help fix this
issue?

Responder

Hi, I do think this is an excellent web site. I stumbledupon it 😉 I
will return once again since I book marked it.
Money and freedom is the greatest way to change, may you
be rich and continue to guide others.

Responder

I am really impressed with your writing skills and also with the layout on your
blog. Is this a paid theme or did you modify it yourself?
Anyway keep up the excellent quality writing, it is rare to see a
great blog like this one these days.

Responder

whoah this blog is fantastic i like studying your posts.
Keep up the great work! You already know, a lot of individuals are searching
round for this info, you can help them greatly.

Responder

Spot on with this write-up, I absolutely believe that this web
site needs a lot more attention. I’ll probably be returning
to read more, thanks for the info!

Responder

Sweet blog! I found it while searching on Yahoo News. Do you have any
tips on how to get listed in Yahoo News? I’ve been trying for a while but
I never seem to get there! Appreciate it

Responder

My brother recommended I might like this blog.
He was entirely right. This post truly made my day. You can not imagine simply how much time
I had spent for this info! Thanks!

Responder

Hmm is anyone else experiencing problems with the images on this blog loading?

I’m trying to find out if its a problem on my end or if it’s the blog.
Any feed-back would be greatly appreciated.

Responder

My spouse and I stumbled over here coming from a different web address
and thought I might as well check things out. I like what
I see so i am just following you. Look forward to going over your web page again.

Responder

After looking into a number of the blog posts on your web page, I honestly
appreciate your technique of blogging. I book marked it to my bookmark website list and will be
checking back in the near future. Take a look at my website too and tell me
your opinion.

Responder

I don’t even know the way I ended up right here, but I
assumed this submit was good. I do not recognize who you might be but definitely you’re
going to a famous blogger in the event you aren’t already.
Cheers!

Responder

I’m curious to find out what blog system you’re working with?
I’m experiencing some minor security issues with my latest site and
I’d like to find something more risk-free. Do you have any suggestions?

Responder

You actually make it seem really easy with your presentation but I in finding this topic to be actually one thing which I feel
I might never understand. It kind of feels too complex
and very wide for me. I’m having a look forward on your subsequent put up, I will try to get the dangle of it!

Responder

I think the admin of this website is really working hard
for his website, because here every material is quality based stuff.

Responder

Good day! Do you know if they make any plugins to protect against hackers?
I’m kinda paranoid about losing everything I’ve worked hard
on. Any tips?

Responder

thanx for this article: I’m doing a lecture I’m presently fighting with, and I’ve been bringing together text like this. This will help a lot!

Responder

Heya just wanted to give you a brief heads up and let you know a
few of the images aren’t loading correctly.
I’m not sure why but I think its a linking issue. I’ve tried it in two different web browsers and both show the same outcome.

Responder

This post is truly a nice one it assists new internet users,
who are wishing in favor of blogging.

Responder

Wonderful blog! I found it while searching on Yahoo News. Do you have any tips on how to get listed in Yahoo News? I’ve been trying for a while but I never seem to get there! Many thanks

Responder