EXCEL - Contar.Si en tabla filtrada

Una vez más recurro a vuestros conocimientos en Excel y/o VBA.

Tengo una tabla en la que necesito conocer los valores únicos de la columna Q. Por ejemplo, si tengo verde, rojo, azul, verde, amarillo, verde, blanco, rojo. El resultado sería = 5

Normalmente, eso lo haría con un CONTAR. SI. El problema es que necesito realizar esa cuenta cuando tengo un filtro aplicado en la columna B. Es decir, que me cuente los registros únicos de las celdas visibles.

He visto esta pregunta muchas veces pero no consigo encontrar una solución que me valga.

1

1 Respuesta

4.704.875 pts. Sancho, si los perros ladran ...

Para contar los registros visibles puedes utilizar la función Subtotales( )

Por ejemplo, para contar los datos del rango B2:B11, utiliza el número de función 3

La fórmula quedaría:

=SUBTOTALES(3;B2:B11)

El resultado solamente cuenta los datos visibles:


[' Si es lo que necesitas. No olvides valorar la respuesta. 

Gracias Dante.

La función Subtotal la conozco pero, en este caso creo que no me sirve... o yo no sé cómo aplicarla. Quizás me he explicado mal pero, lo que necesito son los valores únicos de las filas filtradas.

Es decir, en tu ejemplo, el resultado que necesito es = 2 (azul y verde).

Para resolverlo con fórmulas, necesitamos utilizar 2 columnas.

Por ejemplo:

En la celda C2 pon la siguiente fórmula

=SUMAPRODUCTO(SUBTOTALES(3;DESREF(B2;FILA(B2:$B$10)-FILA(B2);;1))*(B2:$B$10=B2))

Copia o arrastra la fórmula hasta la fila 10.

En la celda D2 pon la siguiente fórmula

=SI(C2>1;"";1)

También copia o arrastra la fórmula hasta C10.

Ahora, en la celda D1 pon la siguiente fórmula:

=SUBTOTALES(2;D2:D10)

En la celda D1 tendrías el resultado, prueba con los siguientes datos:


La otra opción sería una macro.

[Sal u dos

Muchas gracias Dante.

Mañana probaré està opción que me comentas. Aunque si existe una opción que no implique añadir columnas (macro),  me gustaría conocerla... si no es mucha molestia para ti. 

El tema es que el fichero con el que trabajo es realmente un objeto tabla de Excel al que se le aplican diferentes filtros y macros para para exportar "partes" en nuevos archivos, obtener sumatorio,  subtotales, estadísticas, ... Cuanto menos lo modifique, mejor 😅

Muchas gracias de todos modos por tu ayuda! !

Para hacer la macro, pon un par de imágenes para ver cómo tienes los datos, qué resultado esperas y en dónde quieres poner los resultados.

Es un poco complicado pero voy a intentar explicarlo bien:

Hoja PLAN: tengo un objeto Tabla con los encabezados en la línea 9

En la fila 6 tengo una serie de subtotales que utilizo para obtener resultados en función de los filtros que aplico.

En otra hoja COMPLEMENTOS, tengo una serie de botones con  macros para aplicar filtros y copiar los subtotales en celdas de esa hoja COMPLEMENTOS

Lo que quiero hacer es lo mismo pero, en lugar de con subtotales, con los valores únicos de la columna Q ("Equipo").

Es decir, yo indicaría en COMPLEMENTOS una Fecha y un Día de la semana y aplicaría el filtro a la tabla.

Después, necesitaría que en la Q6 me aparecieran los valores únicos de la columna Q (en el ejemplo de la imagen, sería = 3  (equipos 1, 2 y 4)) --> es el nº d equipos que trabajan el domingo para la fecha indicada.

Esa celda Q6 la copiaría y la pegaría en COMPLEMENTOS

Ésta es la macro que utilizo para hacer esta acción:

Sub Subtotales()
Dim Celda As Range, Lista As Range
    Application.ScreenUpdating = False
    Set h1 = Sheets("PLAN")
    Set h2 = Sheets("COMPLEMENTOS")
    'Recorrer las fechas en COMPLEMENTOS (C6:C11) --> copiar fecha en celda de PLAN y aplicar filtro
    For i = 6 To 11
        If h2.Cells(i, "C").Value <> "" Then
            h1.Range("celFecha") = h2.Cells(i, "C").Value
            'Llama a la macro para aplicar el filtro en PLAN. Para ello me sitúo en dicha hoja primero
            h1.Select
            Call LimpiarFiltro
            Call Filtrar_Fechas
            'Copia las celdas con los subtotales en PLAN y las pega en COMPLEMENTOS
            h1.Range("AH6:AL6").Copy
            h2.Cells(i, "D").PasteSpecial Paste:=xlPasteValues        
        End If
    Next
    Application.ScreenUpdating = True
    Application.CutCopyMode = False
        'Limpiar los autofiltros
        h1.Select
        Call LimpiarFiltro
        'Limpiar la celda Fecha plan
        h1.Range("celFecha").Select
        Selection.ClearContents
        h2.Select
End Sub

Después de tu macro, ejecuta sobre tu hoja "PLAN" la siguiente macro:

    Dim contador As New Collection
    On Error Resume Next
    For i = 10 To Range("Q" & Rows.Count).End(xlUp).Row
        If Cells(i, "A").EntireRow.Hidden = False Then
            contador.Add Item:=Cells(i, "Q").Value, Key:=CStr(Cells(i, "Q").Value)
        End If
    Next
    On Error GoTo 0
    Range("Q6").Value = contador.Count

[sal u dos

GENIAL DANTE!! MUCHAS GRACIAS!!

Lo he estado probando y funciona muy bien. Sólo una última cosa para que me quede perfecto: el contador cuenta también las celdas en blanco. ¿Sería posible que contara sólo las que tienen algún dato?

Pensé en ponerle un -1 pero, no me convence. No debería haber celdas en blanco pero, nunca se sabe.

Así:

    Dim contador As New Collection
    On Error Resume Next
    For i = 10 To Range("Q" & Rows.Count).End(xlUp).Row
        if cells(i, "Q").value <> "" then
           If Cells(i, "A").EntireRow.Hidden = False Then
               contador.Add Item:=Cells(i, "Q").Value, Key:=CStr(Cells(i, "Q").Value)
           End If
        end if
    Next
    On Error GoTo 0
    Range("Q6").Value = contador.Count

Añade tu respuesta

Haz clic para o

Más respuestas relacionadas