Suma por color

Si necesita calcular el total de ciertas celdas en función de su fuente o color de relleno, es posible que haya notado que las fórmulas de Excel operan con valores almacenados, no con valores mostrados. Eso significa que funciones como SUMA y SUMIFS operan en los valores de celda subyacentes e ignoran el formato de la celda, como la fuente o el color de relleno. Esta publicación proporciona tres pasos para solucionar este problema y calcular un total según el color de relleno.

Objetivo

Antes de llegar demasiado lejos, echemos un vistazo a lo que estamos tratando de lograr.

Tenemos una variedad de datos y las transacciones seleccionadas se han resaltado con un color de relleno de celda, como se muestra a continuación.

Querremos poder resaltar filas adicionales en cualquier momento y no queremos reescribir ni actualizar nuestra fórmula cuando lo hagamos. Eso significa que nuestro rango de fórmula debe incluir la columna de cantidad y no podemos usar una fórmula que seleccione cada celda (como E10+E12+E15…).

Cuando intentamos escribir una fórmula que sume la columna de monto pero incluya solo las transacciones resaltadas, nos quedamos atascados. Independientemente de qué función de suma usemos, parece que no podemos decirle a Excel que incluya solo las celdas resaltadas. Esto se debe a que las fórmulas operan sobre los valores almacenados subyacentes y no tienen en cuenta el formato de la celda.

Entonces, ¿podemos hacer esto? Sí, podemos, no te preocupes.

Cómo

Podemos lograr nuestro objetivo con la ayuda de dos elementos de Excel, a saber, un filtro y una función SUBTOTAL.

Paso 1: El filtro. Podemos filtrar por fuente o color de relleno utilizando la función de filtro incorporada de Excel. Para activar los filtros, simplemente seleccione cualquier celda dentro del rango de datos y luego el siguiente ícono de cinta:

• Datos Filtrar

Esto activará pequeños controles de filtro, o menús desplegables, en la fila del encabezado. Estos se muestran a continuación.

Con ellos podremos filtrar por color de relleno. Simplemente baje uno y luego seleccione Filtrar por color. Aparecerá un menú deslizable que muestra los colores utilizados dentro de la región de datos. Cuando elige un color, Excel mostrará esas filas y ocultará las demás, como se muestra a continuación.

Una vez que tengamos el filtro funcionando, ya casi terminamos. Todo lo que tenemos que hacer es escribir una fórmula que incluya sólo las filas visibles.

Paso 2: la fila en blanco. Como siempre queremos que nuestra fila total esté visible, no queremos que se incluya dentro del rango del filtro. Para evitar que Excel oculte la fila total al aplicar un filtro, queremos omitir una fila antes de escribir la fórmula. Por lo tanto, no escribiremos nuestra fórmula en la fila inmediatamente debajo del rango de datos, dejaremos una fila en blanco entre la última fila de datos y la fila de la fórmula.

Paso 3: SUBTOTAL. Excel tiene varias funciones que pueden sumar cosas. Para lograr nuestro objetivo, no podemos usar una función SUMA estándar porque incluye filas visibles y ocultas. La función SUBTOTAL, por otro lado, incluye solo filas visibles dentro de una región filtrada. El truco para usar la función SUBTOTAL es establecer el primer argumento en 9 o 109. Cualquiera de los dos funcionaría en este caso porque queremos sumar los resultados y estamos usando un filtro para ocultar las filas. Si nuestra columna de monto estuviera almacenada en el rango E8:E19, entonces usaríamos la siguiente fórmula en nuestra fila de total:

=SUBTOTAL(9, E8:E19)

Esto se muestra a continuación.

Entonces, conectando los puntos… sabemos que podemos usar el filtro para mostrar solo las transacciones resaltadas, sabemos que la fila en blanco entre la última fila de datos y la fórmula ayudará a garantizar que nuestra fila total quede excluida del filtro, y conocemos el SUBTOTAL. La función incluirá solo las filas visibles dentro de la región filtrada. Entonces, ¿funcionará esto? Vamos a averiguar…

¡Sí, funcionó! El total se actualiza de 7560 (todas las filas) a 2377 (filas resaltadas)… ¡hemos calculado un total basado en el color de relleno tal como queríamos!

Si tiene alguna otra técnica divertida para sumar por color u otros usos sugeridos para los filtros, compártala publicando un comentario a continuación… ¡gracias!

Notas

• Descargue el archivo Excel: SumByColor
• Esta técnica funciona si los datos también se almacenan en una tabla. En lugar de escribir la fórmula SUBTOTAL manualmente, Excel la agregará automáticamente cuando marquemos la casilla Fila total en la pestaña de la cinta de TableTools. Además, los controles de filtro se incluirán automáticamente en la fila del encabezado de la tabla.
• Si su fila total se incluye en el filtro (y, por lo tanto, se oculta), asegúrese de tener una fila en blanco entre la última fila de datos y la fila de fórmula y luego desactive los filtros (Datos Filtro) y luego vuelva a activarlos (Datos Filtro). ).