FUNCION CONTAR.SI EN VBA

Hoy es el primer post que escribo después del nacimiento de mi hija Alicia, sin duda la mejor experiencia de mi vida!!. Han sido unos días muy intensos y felices y por fortuna todo ha salido muy bien.

Como comprenderéis no tengo demasiado tiempo últimamente, y ese el motivo de tardar tanto tiempo en publicar una actualización.

El tema de hoy es una consulta que me envían al blog sobre la función “Contar.Si” y la posibilidad de automatizarla en una hoja Excel mediante macros.

Me ha parecido un ejercicio interesante, sobre todo ver la forma en la que podemos introducir una fórmula en el código que vamos a utilizar. ¿Empezamos?

Como viene siendo habitual intentaré ilustrar el proceso con un ejemplo, que es la mejor forma de aprender. Imaginad que tenéis un listado en una columna con 199 nombres, (en el ejemplo he utilizado las bases de datos del INE en donde podéis encontrar, por ejemplo, los nombres más usados en los últimos nacimientos). Estos son los nombres:

FUNCION CONTAR.SI EN VBA

Y nos piden dos consultas:
– Contar las veces que se repiten cada uno de los nombres y colocar el resultado en la columna B.
– Contar las veces que se repiten unos nombres en particular, y colocar el resultado en la columna C.

Para la primera consulta utilizaremos la siguiente macro:

Sub ContarSi()
Dim i As Double
Dim final As Double
'Limpiamos los contenidos de la columna B
Worksheets("DATOS").Range("B2:B65000").Select
Selection.ClearContents
'Calculamos el rango de los datos en la columna A
Worksheets("DATOS").Select
final = Application.CountA(Worksheets("DATOS").Range("a:a"))
For i = 2 To final
'Contamos las veces que se repiten cada uno de los nombres en el rango seleccionado
Nombres = Worksheets("DATOS").Cells(i, 1).Value
Worksheets("DATOS").Cells(i, 2).Value = Application.CountIf(Worksheets("DATOS").Range("A1:A" & final), Nombres)
Next
End Sub

Como podéis apreciar, incluimos la función Contar.Si mediante la siguiente sentencia:

Application.CountIf(Worksheets("DATOS").Range("A1:A" & final), Nombres)

De esta forma hacemos referencia a la sintaxis del rango establecido en el que contaremos Range(“A1:A” & final) y el criterio que es “Nombres” y que es igual a Worksheets(“DATOS”).Cells(i, 1).Value

El resultado de la macro lo colocaremos en la segunda columna, quedando así la información:

FUNCION CONTAR.SI EN VBA_1

Para la segunda consulta utilizaremos la siguiente macro:

Sub ContarSiV()
Dim i As Double
Dim final As Double
'Limpiamos los contenidos de la columna C
Worksheets("DATOS").Range("C2:C65000").Select
Selection.ClearContents
'Calculamos el rango de los datos en la columna A
Worksheets("DATOS").Select
final = Application.CountA(Worksheets("DATOS").Range("a:a"))
For i = 2 To final
Nombres = Worksheets("DATOS").Cells(i, 1).Value
'Mediante una condición contamos las veces que se repiten los nombres elegidos
If Nombres = "IGNACIO" Or Nombres = "MARIA" Or Nombres = "TERESA" Then
Worksheets("DATOS").Cells(i, 3).Value = Application.CountIf(Worksheets("DATOS").Range("A1:A" & final), Nombres)
End If
Next
End Sub

En este caso estamos delimitando que la función solamente cuente aquellos nombres que hemos establecido en la condición (IGNACIO, MARIA Y TERESA), el resto quedan en blanco. El resultado es el siguiente:

FUNCION CONTAR.SI EN VBA_2

Para facilitar las pruebas os he colocado dos botones para ejecutar cada uno de los códigos por separado.

Este ejemplo también puede ser muy útil para introducir cualquier tipo de función, tan solo tendréis que adaptar la sintaxis a VBA, por ejemplo: sumar.si, sumaproducto, contar.si.conjunto … etc.

Espero que os haya resultado de utilidad y que lo podáis integrar con éxito en vuestros proyectos de VBA

Descarga el archivo de ejemplo pulsando en: FUNCION CONTAR.SI EN VBA

35 pensamientos en “FUNCION CONTAR.SI EN VBA

  1. Excelente aplicativo estimado……..Le comento se puede adaptar este aplicativo o se puede hacer uno que lo pueda utilizar en consulta de 2 hojas en Excel en el mismo libro y/o dos libros distintos. Gracias

    Me gusta

  2. Hola Jose;

    Pues sí que se puede hacer lo que comentas, puedes verlo en el ejemplo que he subido a Google Drive pinchado en el siguiente enlace:
    https://drive.google.com/folderview?id=0B2MZVcPxjhyPflgtNDB5aHVUSkxPVkd1QzZyME9TZmFKa2FscUJNWTMwVklpblFVcWZ4YTA&usp=sharing

    Encontrarás una carpeta con dos archivos, uno de ellos es FUNCION CONTAR.SI EN VBA contiene las macros y los ejemplos para ir a consultar a otra página dentro del archivo. El otro archivo es parte del ejemplo para que pruebes a contar en “OTRO_LIBRO”.

    Para contar en otro libro, la forma de hacerlo y que me parece la mejor, es traerte la información que quieres contar al archivo, en nuestro caso lo pegaríamos en la pestaña BASE2

    Para traer la información he usado una macro con SQL que copia la info de otro archivo. Lo único que debes hacer es poner en la macro denominada “contarsi_otro_libro()” la ruta en la que guardes el archivo “OTRO_LIBRO”, en la macro he dejado la ruta en mi PC: C:\Users\USUARIO\Documents\FUNCION CONTAR.SI EN VBA\OTRO_LIBRO.xls

    Si no modificas esta ruta en la macro, no funcionará y saltará un error.

    Para tu información, la macro para importar los datos de otro libro está en esta entrada: https://excelsignum.wordpress.com/2015/04/25/realizar-consulta-sql-desde-vba-en-excel-hacer-una-consulta-de-access-en-excel/

    Espero te sirvan de ayuda.

    Saludos.

    Me gusta

  3. Saludos amigo, soy muy novato en el tema de las macros pero recién estoy fascinado con ellas… el caso es esta macro que usted publica en el articulo es “exactamente” lo que estaba buscando salvo que lo que deseo contar son números, usted me podría ayudar con ello, gracias de de antemano

    Me gusta

    • Hola Luis:

      Pues contar números se hace de la misma forma que letras, es decir, en lugar de nombres pon datos numéricos y verás como la macro cuenta cuantas veces se repiten esos números. Solo debes cambiar los datos a contar.

      Saludos.

      Me gusta

  4. Mil gracias por tan pronta respuesta, estoy seguro que así de fácil como usted indica, pero por mas que intento remplazar los nombre por los números no consigo que funcione, le estaría eternamente agradecido si me ayuda con este problema (tome en cuenta que solo tengo una semana en este mundillo) mi problema es que deseco contar las veces que se repite un numero en la columna (M3:M108) y el resultado se refleje en la columna (N3:N108), Nota: los números que me enteriza contar es del 0 al 99, en verdad hermano le estaré muy agradecido si es de su agrado ayudarme.

    Me gusta

  5. pido de su apoyo, todo con lo la Funcion contar esta entendido, pero ahora, como se sacaria el resultado, de esas dos columnas, pero ejemplo, tengo grupos de escuelas de 1ro, 2do y 3er anios, lo mismo pero se repite el en una columna 3 primeros, 2 segundos y 4 terceros, nunca en orden como lo puse separados por el tema de orden de lista, bueno y en otras dos columnas, se encuentran altas y bajas por separado, y en altas hay 3 ninios, en bajas 6 ninios, como saco el resultado por separa de los tres grados en altas y bajas de 1ro, 2do y 3ro

    Me gusta

    • Hola Benjy:

      Para poder contestar a tu pregunta, necesitaría que me enviases un archivo con los datos como los tienes ahora, y en otra hoja que quieres que te queden. Y una explicación detallada de lo que debe realizar la macro. Saludos.

      Me gusta

  6. Muy interesante y loable de tu parte compartir tus conocimientos, mis respetos !!!

    llegue aquí por que hace días me trae complicado una macro que realice la función SUMAR.SI con varios documentos que están en una misma carpeta.

    Actualmente tengo una macro que suma una celda “x” en diferentes archivos que es esta:

    Sub M2porMes()
    Dim MyBook As String, myForm As String
    Const Celda As String = “i26”
    With ThisWorkbook
    MyBook = Dir(.Path & “\*.Xls”)

    Do
    If MyBook .Name Then
    myForm = myForm & ” + ‘” & .Path & “\[” & MyBook & “]Hoja1’!” & Celda
    End If
    MyBook = Dir
    Loop Until MyBook = “”
    End With

    ActiveCell.Formula = “=” & myForm

    Rem — La siguiente línea es optativa —-
    ActiveCell = ActiveCell.Value
    End Sub

    funciona a la perfeccion pero ahora se han sumado variables a los productos que se utilizan, es por eso que necesito que sume solo si se cumple “x” variable, como por ejemplo:

    =SUMAR.SI.CONJUNTO(I11:I25;L11:L25;”INTERCOAT”)

    espero me puedas ayudar

    Muchas Gracias

    Me gusta

    • Hola Manuel:

      Tendrías que pasar la fórmula a VBA, que es esta: Application.WorksheetFunction.SumIfs(Rango de la suma, rango criterio, criterio).

      Por ejemplo:

      Dim i As Integer
      With Sheets(1)
      For i = 2 To 12
      .Cells(i, 4).Value = Application.WorksheetFunction.SumIfs(.Range(“a1:a5000”), .Range(“b1:b5000”), “x”)
      Next
      End With

      Saludos.

      Me gusta

  7. Segu:
    me ha gustado tu manera de utilizar laa función Contar, por lo que m atrevo a consultarte: Cómo utilizar la función Contar.Si, si el rango de celdas que voy a contar varía al realizar modificaciones; se me ocurre ejemplificártelo conla disponibilidad de un lote de vehículos, alinicio TODOS los vehículos están disponibles, pero de acuerdo a como han salido de viaje o por desperfectos, algunos están indisponibles;pero al retornar o repararlos, nuevamente están disponibles.
    Espero haberme hecho entender,
    Gracias de antemano
    Loroloco (Guatemala)

    Me gusta

  8. Hola, tengo una consulta. El countif no diferencia entre mayusculas y minusculas. Si tengo el nombre “CASA” y en otra celda “casa” dirá que es duplicado pero yo necesito que lo diferencie. Alguna idea?
    muchas gracias

    Me gusta

    • Hola Alonso:

      Debes utilizar otro tipo de función. Sumaproducto.

      =SUMAPRODUCTO(IGUAL(A1:A5; “casa”)*1)
      o asi:
      =SUMAPRODUCTO(–IGUAL(A1:A5; “casa”))
      De esta forma obtendrás la información que necesitas.

      Saludos

      Me gusta

  9. Buenas tardes que modificaciones uno debe realizar para realizar el contar si pero con mas de dos criterios y también teniendo en cuenta que en un libro pueden estar varios datos de consulta y en otro libro las bases de datos.

    Me gusta

    • Hola Manuel:

      Debes utilizar la función CONTAR.SI.CONJUNTO (no CONTAR.SI). Un ejemplo en VBA haciendo referencia a la base de datos en otro libro sería así:

      contar = Application.WorksheetFunction.CountIfs(Workbooks(“MI_OTRO_LIBRO.xls”).Sheets(“Hoja1”).Range(“A:A”), ThisWorkbook.Sheets(“Hoja1”).Range(“A2”), Workbooks(“MI_OTRO_LIBRO.xls”).Sheets(“Hoja1”).Range(“B:B”), “>20”)

      Saludos.

      Me gusta

  10. Hola buenas.

    Yo busco una macro que cuente coincidencias de nuemros, pero por filas de 6 celdas.

    Ejemplo: datos B2:G20000 Resultado en I

    Gracias.

    Un Saludo

    Me gusta

  11. La función contar.si trabaja con rangos, por lo que en la macro con solo modificar el rango donde se cuentan los criterios, debería hacer lo que indicas: por ejemplo Range(“A1:E” & final aquí seleccionas desde la columna A a la E.

    Saludos.

    Me gusta

    • Hola Eugenio:

      Lo que indicas requiere un desarrollo mediante programación. En estos momentos tengo varias consultas que estoy programando, cuando las finalice intento ponerme con ello.

      Saludos.

      Me gusta

  12. Buenas tardes,

    La fórmula me ha ayudado muchísimo! Tengo una consulta, si por ejemplo tengo una dos datos duplicados en la columna “A” con esta macro, lo que me aparece en la celda de la derecha, es decir en la columna “B”, es el número total de veces que este dato se repite. Mi consulta es si al igual que con la función contar.si, quede el primer registro de ese dato, lo cuente como “1” y el segundo como “2”.

    Para explicarme mejor, si el valor de la celda A1 y A2 son iguales, utilizando la macro, en la celda B1 saldía 2, y en la celda B2 también saldría 2, por el número de veces que se ha contado; sin enbargo usando solo la fórmula sin macro, me figura en la celda B1 el valor de 1 y en la celda B2 el valor de 2, quisiera saber si con esta magnífica macro se puede lograr ese detalle, ya que para mi es importante que cada fila y registro tenga un número independiente, y si se repite que el siguiente registro salga con2, luego con 3 y luego con 4 en caso de que en el rango se repita 4 veces ese mismo registro.

    No sé si me dejé explicar, pero me auidaría muchísimo.

    Muchas Gracias!!!

    Me gusta

    • Hola Martínez:

      Eso lo puedes realizar de dos maneras:

      Desde el mismo excel del post, en la columna D pon esta fórmula en la celda D2 (previamente debes ordenar la columna A)
      =SI(A2=A1;D1+1;1)

      También lo puedes hacer con CONTAR.SI, en la columna E y en la celda E2 por esta fórmula
      =CONTAR.SI($A$2:A2;A2)
      Aquí no es necesario ordenar.

      Saludos.

      Me gusta

  13. Hola, Segu, gracias por responder!

    Estuve intentando e intentando y bueno, siento no expresarme bien, lo que pasa es que yo lo necesitaba en macro, y de tanto intentar, lo conseguí de la siguiente forma:

    ************************
    Sub ContarSi()

    Dim Lastrow As Long

    Application.ScreenUpdating = False

    Lastrow = Range(“D” & Rows.Count).End(xlUp).Row
    Range(“E2”).formula = “=COUNTIF(RC[-1]:R[70000]C[-1],RC[-1])”
    Range(“E2”).AutoFill Destination:=Range(“E2:E” & Lastrow)
    ActiveSheet.AutoFilterMode = False
    Columns(“E”).AutoFilter Field:=1, Criteria1:=”#DIV/0!”
    ActiveSheet.AutoFilterMode = False

    Application.ScreenUpdating = True

    End Sub
    ************************

    Según la macro, la columna E se rellenaría o con “2” o con “1” ya que los registros que existen en la columna D, se pueden repetir máximo dos veces.

    Sabiendo que mi excel es una base que puede aumentar o disminuir en filas, quería saber si con esa macro que coloco arriba, no habría ningún tipo de problemas. Mi base de datos es la compra de productos para un stock, al cual se le añade estatus (en la columna G) correspondiente a los movimientos que se realicen de un determinado producto.

    Otra consulta, Segu, no sé si me podrías ayudar u orientarme. Tengo que hallar la forma que se eliminen filas con condiciones en dos columnas, mi conocimiento en excel es básico, y he visto tutoriales como el tuyo, en donde se elimina filas según la condición de una sola columna, o si es en varias columnas, es porque las celdas tienen el mismo valor, o son vacías o son 0. Lo que yo quiero, es que en una columna, la celda tenga un valor y en la otra columna, tenga otro valor estipulado para recién eliminar, y si se cumple la condición solo en una columna, no eliminarla, ya que debe cumplirse en ambas. Además, si se elimina una fila, que la que viene inmediatamente después, se elimine

    Esto es masomenos lo que quiero:

    A D E G
    1 18/01/2019 15 2 P
    2 17/03/2019 15 1 R
    3 20/03/2016 20 2 H
    4 02/05/2019 20 1 R
    5 03/10/2008 21 1 R
    6 20/10/2019 25 1 H

    Columna A = fechas de compra
    Columna D = código de un producto en específico
    Columna E = se llena con la macro ContarSi teniendo la columna D como guía
    Columna G = estatus de compra

    *Las columnas que no figuran están ocultas.
    **Lo que yo hice antes de ejecutar la macro ContarSi es hacer orden personalizado; como primer nivel que me ordene el código del producto de menor a mayor y en segundo nivel, que la fecha sea de la más antigüa a la más reciente.

    >>Lo que necesito es crear una macro que identifique el número 2 en la columna E, y a su vez en la columna G, identifique si el valor es diferente a “P” o a “E”, para recién eliminar esa fila y la fila siguiente. En caso que en en la columna E sea 2, pero en la columna G, el estatus sea “P” o sea “E”, no eliminar nada, y asu vez si en la columna G, el valor es diferente a “P” o a “E”, pero en la columna E el valor es 1, no se elimine nada.

    Es decir, al ejecutar la macro debe quedar así:

    A D E G
    1 18/01/2019 15 2 P
    2 17/03/2019 15 1 R
    5 03/10/2008 21 1 R
    6 20/10/2019 25 1 H

    Se eliminan las filas 3 y 4, la 3 por la condición de ambas columnas, y la 4 porque era la columana siguiente a la que fue eliminada.

    Yo creo que al programar la macro con la condición, debería pedirle que primero elimine la columna siguiente y luego la columna que cumpla la condición, o me sugieres otro método?

    En serio muchas gracias si pudieses guiarme o solo decirme que tutorial debo ver para entender mejor, me ayudarías muchísimo.

    Que tengas un excelente día, y gracias por crear tutoriales así y subirlos! No sabes la gran ayuda que me has dado!

    Me gusta

  14. Hola, Segu, podras ayudarme? No se que fórmula aplicar para conseguir arrojar en estas celdas
    el total de ABARROTES, y en la otra celda el total de MASCOTAS:

    no lo consigo con función y con macro no tengo ni idea.

    Me gusta

    • Hola Graciela:

      Tendría que ser algo así, en VBA (obviamente).

      Hoja1.range(“A1”)= Application.WorksheetFunction.SumIfs(Hoja2.Range(“a1:a5000”), .Hoja2.Range(“b1:b5000”), “TUCRITERIO”)

      Saludos.

      Me gusta

¿Te ha gustado?, Realiza un comentario.

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión /  Cambiar )

Google photo

Estás comentando usando tu cuenta de Google. Cerrar sesión /  Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión /  Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión /  Cambiar )

Conectando a %s

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios .