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 respuesta
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
