Brindle Foods dota al 100% cualquier factura vencida hace más de 90 días o en disputa. En el cierre de septiembre, la hoja dotó más de lo que debían sus clientes. Cada factura y cada condición eran correctas; la fórmula sumó dos totales que se solapaban.
| Provisión de cierre, 30 de septiembre | Cifra |
|---|---|
| Facturas abiertas | 14 |
| Saldo de clientes | 83.715,50 £ |
| Más de 90 días | 47.400,50 £ |
| En disputa | 43.295,00 £ |
| Provisión registrada | 90.695,50 £ |
| Provisión debida | 60.800,50 £ |
| Exceso | 29.895,00 £ |
Alcance.
SUMAR.SI.CONJUNTO,SUMAPRODUCTOyOfuncionan en todas las versiones actuales de Excel.FILTRAR, en la sección 6, requiere Excel 2021 o Microsoft 365.En todo el artículo, el saldo está en A1:G15: factura en A, cliente en B, importe en C, días de retraso en D y
YesoNosegún haya disputa en E.
01La Provisión Superaba a la Deuda
La política cabe en una frase: dotar al 100% la factura vencida hace más de 90 días o la que el cliente disputa. El asistente de finanzas la construyó con dos SUMAR.SI.CONJUNTO, uno por condición, y los sumó.
=SUMAR.SI.CONJUNTO(C2:C15;D2:D15;">90")+SUMAR.SI.CONJUNTO(C2:C15;E2:E15;"Yes")
Devolvió 90.695,50 £. Todo el saldo de clientes es 83.715,50 £, así que la hoja esperaba perder 6.980,00 £ más de lo que nadie debía.
El asiento se contabilizó, y las cuentas provisionales llegaron al consejo con 29.895,00 £ menos de beneficio.
Catorce Facturas Abiertas, 83.715,50 £ Pendientes y 90.695,50 £ Dotados
El saldo de clientes de Brindle Foods a 30 de septiembre, en A1:G15. La columna C es el importe pendiente, la D los días de retraso y la E si el cliente lo disputa. La columna F es lo que dotaron los dos SUMAR.SI.CONJUNTO sumados para cada factura, y la G lo que dice la política: el importe completo una sola vez si la factura supera los 90 días o está en disputa.
fxLas celdas con fórmulas están resaltadas en verde
Pasa el mouse sobre las celdas con fórmulas para ver la fórmula y resaltar las celdas referenciadas
El auditor lo encontró en la revisión analítica, al poner la provisión junto al saldo. En Brindle nadie lo había hecho, porque las dos cifras vivían en pestañas distintas.
02Dos Totales Se Suman, No Se Combinan
Cada SUMAR.SI.CONJUNTO es correcto por separado. El primero suma las seis facturas de más de 90 días; el segundo, las cinco en disputa.
Al sumarlos, cada factura que está en los dos grupos cuenta dos veces, porque nada le dice al segundo total lo que ya tomó el primero.
| Grupo | Facturas | Importe |
|---|---|---|
| Más de 90 días | 6 | 47.400,50 £ |
| En disputa | 5 | 43.295,00 £ |
| Ambas | 3 | 29.895,00 £ |
| Alguna, una sola vez | 8 | 60.800,50 £ |
Seis más cinco son once, pero solo ocho facturas cumplen la política. INV-2203, INV-2215 e INV-2226 son antiguas y están en disputa, y cada una entró dos veces en la provisión.
Dentro de un mismo SUMAR.SI.CONJUNTO, todos los criterios son Y. No hay interruptor para O, y por eso se recurre al signo más.
Escenario: Escribe =CONTAR.SI.CONJUNTO(D2:D15;">90")+CONTAR.SI.CONJUNTO(E2:E15;"Yes") junto al saldo y comprueba que devuelve 11. Después cuenta a ojo las facturas que cumplen alguna condición y localiza las tres que explican la diferencia.
03Resta el Solapamiento
El arreglo más rápido conserva los dos totales y resta la parte que comparten. Un tercer SUMAR.SI.CONJUNTO con las dos condiciones mide exactamente lo que se contó dos veces.
=SUMAR.SI.CONJUNTO(C2:C15;D2:D15;">90";E2:E15;"Yes")
Devuelve 29.895,00 £, las tres facturas que están en ambos grupos. Réstalo de la suma de los otros dos:
=SUMAR.SI.CONJUNTO(C2:C15;D2:D15;">90")+SUMAR.SI.CONJUNTO(C2:C15;E2:E15;"Yes")
-SUMAR.SI.CONJUNTO(C2:C15;D2:D15;">90";E2:E15;"Yes")
El resultado es 60.800,50 £. Funciona con dos condiciones. Con tres hacen falta siete SUMAR.SI.CONJUNTO, sumando y restando por turnos, y nadie lo acierta en pleno cierre.
Escenario: Escribe primero el SUMAR.SI.CONJUNTO del solapamiento y comprueba que devuelve 29.895,00. Después construye la fórmula de tres partes y confirma que la provisión baja a 60.800,50.
04Cuenta la Fila, No las Condiciones
SUMAPRODUCTO puede probar cada fila contra las dos condiciones y decidir una sola vez si cumple. Cada comparación devuelve VERDADERO o FALSO, y al sumarlas sale 0, 1 o 2 por fila.
=SUMAPRODUCTO(C2:C15*(((D2:D15>90)+(E2:E15="Yes"))>0))
El >0 es todo el arreglo. Convierte el 2 en un único sí, así que INV-2203 cuenta una vez, como cualquier otra fila que cumple. Sin él, la fórmula repite el error original.
Sumar condiciones así escala bien: una tercera condición es un paréntesis más dentro de la suma, no cuatro SUMAR.SI.CONJUNTO más. Para contar facturas en lugar de sumarlas, quita los importes.
=SUMAPRODUCTO(--(((D2:D15>90)+(E2:E15="Yes"))>0))
Devuelve 8, donde los dos CONTAR.SI.CONJUNTO sumados devolvían 11.
Escenario: Escribe el SUMAPRODUCTO con >0 y comprueba que devuelve 60.800,50. Después borra el >0 y sus paréntesis, y mira cómo vuelve a subir a 90.695,50.
05Una Columna Auxiliar que Se Lee
Una fórmula única es difícil de auditar. Una columna que dice, fila a fila, si cada factura cumple es fácil de auditar. Escribe esto en H2 y rellénalo hacia abajo:
=O(D2>90;E2="Yes")
O devuelve un solo VERDADERO por muchas pruebas que se cumplan. La provisión pasa a ser un SUMAR.SI.CONJUNTO corriente sobre esa columna, =SUMAR.SI.CONJUNTO(C2:C15;H2:H15;VERDADERO), y =CONTAR.SI(H2:H15;VERDADERO) devuelve 8.
Si prefieres ver importes en lugar de marcas, pon el importe directamente en la columna. Así se construyó la columna G del ejemplo:
=SI(O(D2>90;E2="Yes");C2;0)
Un revisor puede filtrar por VERDADERO y puntear ocho facturas contra la política. Nadie puede hacer eso con un número que sale de una sola celda.
Escenario: Añade la columna con O en H y fíltrala por VERDADERO. Comprueba que quedan ocho facturas, que INV-2223, con 90 días justos, no está entre ellas, y que =SUMAR.SI.CONJUNTO(C2:C15;H2:H15;VERDADERO) devuelve 60.800,50.
06FILTRAR Devuelve Cada Fila Una Vez
En Excel 2021 o Microsoft 365, FILTRAR acepta las mismas condiciones sumadas y devuelve cada fila cuya suma no es cero. Una fila que cumple las dos condiciones sigue siendo una fila.
=SUMA(FILTRAR(C2:C15;(D2:D15>90)+(E2:E15="Yes")))
FILTRAR trata como un sí cualquier valor de inclusión distinto de cero, así que el 2 de INV-2203 no necesita >0. El total es 60.800,50 £.
Quita la SUMA y la misma fórmula lista los ocho importes, algo útil para imprimir junto al asiento. FILTRAR(A2:C15; …) lista también las facturas.
07Un O en una Misma Columna Es Seguro
El doble recuento necesita dos columnas. Cuando las dos condiciones prueban la misma columna, ninguna fila puede cumplir ambas, así que sumar está bien.
Una lista de clientes bloqueados en H2:H3 con Corran Hotels y Tenby Care Homes suma sin problemas:
=SUMA(SUMAR.SI.CONJUNTO(C2:C15;B2:B15;H2:H3))
SUMAR.SI.CONJUNTO se ejecuta una vez por nombre y SUMA suma los resultados: 20.020,00 £ de Corran y 17.295,00 £ de Tenby, 37.315,00 £ en total. Cada factura es de un solo cliente, así que nada se solapa.
Se rompe en cuanto la lista se repite. Escribe Corran Hotels en H2 y en H3 y el total se duplica hasta 40.040,00 £, sin ningún error.
Los tramos solapados son el mismo problema en una sola columna.
">30"y">90"sobre los días se solapan en cada factura de más de 90 días.La prueba no es la columna; es si una misma fila puede cumplir los dos criterios.
Escenario: Pon Corran Hotels en H2 y Tenby Care Homes en H3, y escribe =SUMA(SUMAR.SI.CONJUNTO(C2:C15;B2:B15;H2:H3)). Comprueba que devuelve 37.315,00; después cambia H3 a Corran Hotels y mira cómo se duplica el total.
08Compara la Provisión con la Deuda
Dos comprobaciones habrían frenado el asiento. Ninguna factura puede dotarse por más de lo que vale, y ninguna provisión puede superar el saldo que cubre.
=SUMA(F2:F15)<=SUMA(C2:C15)
En la hoja de Brindle devuelve FALSO. Factura a factura, =SUMAPRODUCTO(--(F2:F15>C2:C15)) devuelve 3, una por cada factura dotada dos veces.
La primera comprobación solo detecta el caso extremo: un solapamiento menor que las facturas sin dotar la pasaría. La segunda detecta cualquier doble recuento, así que ponla junto al asiento, no en una pestaña que nadie abre.
Escenario: Escribe =SUMAPRODUCTO(--(F2:F15>C2:C15)) encima de la columna F y comprueba que devuelve 3. Después sustituye la columna F por la fórmula SI(O(…)) de la sección 5 y confirma que baja a 0.
09Seis Trampas
- Sumar
SUMAR.SI.CONJUNTOpara hacer un O. Las filas que cumplen ambas condiciones cuentan dos veces, sin error. - Olvidar el
>0.SUMAPRODUCTOcon condiciones sumadas y sin>0repite el doble recuento. - Multiplicar en lugar de sumar.
*entre condiciones es Y, no O; devuelve solo el solapamiento de 29.895,00 £. - Un nombre repetido en la lista de criterios.
SUMA(SUMAR.SI.CONJUNTO(…;H2:H3))cuenta dos veces a un cliente que aparece dos veces. - Tramos solapados en una columna.
">30"más">90"cuenta dos veces las facturas más antiguas. >90frente a "90 días o más". INV-2223 tiene 90 días justos, así que la redacción de la política decide si entra.
10Cuatro Ejercicios
- Reprodúcelo. Suma dos
SUMAR.SI.CONJUNTO, uno por más de 90 días y otro por disputa, y confirma 90.695,50. - Mide el solapamiento. Escribe el
SUMAR.SI.CONJUNTOcon las dos condiciones y réstalo. - Cuenta cada fila una vez. Usa
SUMAPRODUCTOcon>0y después la columna auxiliar conO, y comprueba que las dos devuelven 60.800,50. - Demuéstralo. Añade la comprobación por factura y confirma que marca INV-2203, INV-2215 e INV-2226, y nada más.
Qué Llevarse
El saldo de Brindle era correcto y sus dos totales también. La hoja los sumó, y una factura antigua y en disputa entró dos veces en la provisión.
Un O entre dos columnas no es un signo más. Decide una vez por fila si cumple, con O, con >0 o con FILTRAR, y después suma las filas.
Y pon la provisión junto al saldo, porque nunca puede ser la cifra mayor.