Supongamos que por algún motivo no podemos operar con el rango A1:A8. Si queremos sumar todos los valores en los dos rango podemos usar la fórmula
=SUMA(A1:A3,A5:A8)
Lo mismo si queremos contar cuántos elementos hay en ambos rangos
=CONTAR(A1:A3,A5:A8)
Pero si queremos usar CONTAR.SI para averiguar cuantos números mayores a 200 hay en los dos rangos, la fórmula
=CONTAR.SI((A1:A3,A5:A8),">200")
da como resultado #¡VALOR! Lo mismo sucede si queremos usar SUMAR.SI
La solución, por lo general, será combinar dos funciones CONTAR.SI. En nuestro caso sería
=CONTAR.SI(A1:A3,">200")+CONTAR.SI(A5:A8,">200")
El problema con esta solución es que en muchos casos tenemos que combinar muchas funciones CONTAR.SI, lo que convierte en engorrosa la tarea de construir nuestra fórmula.
Veamos cuáles son nuestras posibilidades:
Una posibilidad más compacta es ésta (propuesta por Juan Pablo González en el foro de MrExcel):
=SUMA(CONTAR.SI(INDIRECTO({"A1:A3";"A5:A8"}),">200"))
o utilizar la función ARRAY.JOIN del complemento Morefunc que tantas veces he recomendado
={SUMA((ARRAY.JOIN(A1:A3,A5:A8)>200)*1)}
Esta última fórmula es matricial y debe introducirse en la celda apretando simultáneamente Ctrl+Mayúsculas+Enter.
Los "puristas" pueden utilizar también
=SUMA(--(ARRAY.JOIN(A1:A3,A5:A8)>200))
donde usamos el doble signo menos (--) para forzar la conversión de valores lógicos a 1 (VERDADERO) o 0 (FALSO).
Ambas fórmulas dan como resultado 6.
Para sumar condicional podemos usar estas posibilidades:
=SUMA(SUMAR.SI(INDIRECTO({"A1:A3";"A5:A8"}),">200"))
una variante de la fórmula con INDIRECTO, adaptada a suma condicional, o
=SUMAPRODUCTO((ARRAY.JOIN(A1:A3,A5:A8)>200)*ARRAY.JOIN(A1:A3,A5:A8))
donde usamos SUMAPRODUCTO junto con ARRAY.JOIN
Podemos, y es recomendable, usar rangos nominados. Definimos dos nombres, cada uno refiriéndose a cada uno de los rangos
rango1 =Hoja1!$A$1:$A$3
rango2 =Hoja1!$A$5:$A$8
y utilizarlos en nuestras fórmulas. Por ejemplo:
=SUMA(SUMAR.SI(INDIRECTO({"rango1";"rango2"}),">200"))
Habrán notado que en las fórmulas con INDIRECTO usamos la expresión {"A1:A3";"A5:A8"}. Esta expresión crea un matriz en base a los datos de los rangos expresados como texto (que aparecen entre comillas y por ese motivo usamos INDIRECTO). Pueden consultar esta nota sobre funciones y constantes matriciales.
Technorati Tags: MS Excel
Hola maestro!!, buenisimo tu blog, felicitaciones. es un excelente lugar para satisfacer dudas a las cuales microsoft no nos da una respuesta fácil.
ResponderBorrarTe escribo, por que tengo una duda desde hace ya varios meses. quizás esté en este blog, o bien es muy básica como para ello. Aquí va; quiero hacer una pestaña desplegable con opciones determinadas (como por ejemplo nombres de personas) y que cuando seleccione uno, en otra casilla se disponga un valor relacionado con ese nombre. esto, sin la utilización de macros.
muchas gracias, y nuevamente felicitaciones por tu blog
diego
Hola Diego
ResponderBorrarla lista desplegable la haces con Validación de datos. El valor relacionado con la selección de la lista desplegable lo obtienes con alguna función de búsqueda, por lo general BUSCARV.
Puedes buscar información sobre ambos temas en mi blog.
Hola jorge, necesito ayuda, como se anidada buscarv con la funcion si y O la funcion si o; cual es el razonamiento que tengo que hacer, como indicarle que si es a ( pero lo tengo que buscar en otra lista, reciba tal sueldo? no entiendo como seguir los pasos, muchas gracias por la ayuda
ResponderBorrarsaludos
Creo que la técnica que muestro en esta nota te será útil
ResponderBorrarHola Jorge:
ResponderBorrarTengo una columna A2:A50 con las fechas de nacimiento de un grupo de personas, en la columna C2:C50 estan las edades de cada una de las personas (las calcule a partir de la formula =SIFECHA(A2,HOY(),"Y") ) en la columna AB2:AB50 esta el sexo de dichas personas;
a partir de la fórmula =SUMAPRODUCTO(--(SIFECHA(A2:A50,HOY(),"y")<30),--(SIFECHA(A2:A50,HOY(),"y")>=0)), logré determinar los grupos de edad,
el PROBLEMA ahora es que tengo que saber en cda grupo de edad cuantas mujeres y cuantos hombres hay, por ejemplo menores de 30 años cuantos hombre y cuantas mujeres, espero me puedan ayudar porque ya lo intenté de mil formas y no logro obtener el dato.
Gracias de Antemano,
Saludos
Alexx
Los Mochis, Sinaloa, México
alexxxo,
ResponderBorrartu comentario no está relacionado con el tema de la nota (que para eso están los comentarios).
Estás envíado a hacerme la consulta por mail privado, tal como pongo en el enlace "Ayuda".
muy agradecido por tu blog, paso a detallarte mi pregunta, mediante una macro voy llenando una tabla que tiene 14 filas, hasta aquí todo bien mi problema viene cuando se acaban esas 14 filas, que solución sería mejor, modificar la macro para cada hoja? se puede crear tablas con rango discontinuo en la misma hoja?no puede ser continuo pues la tabla tiene a parte del encabezado un logo y algun dato mas y al final de las catorce filas otros datos que quiero q salgan al imprimir, un saludo y reitero mi agradecimiento
ResponderBorrarLa solución es construir el modelo siguiendo las normas de buenas prácticas, entre ellas la separación entre datos y reportes. Tienes que poner los datos en una hoja (sin logo y nada al final de la tabla, solo datos) y el reporte, con logo y todo lo que haga falta en otra hoja.
ResponderBorrarAl poner los datos en una hoja separda se eliminan los rangos discontinuos. El código de la macro hay que escribirlo con una variable que se ajuste a la cantidad de filas de la tabla.
En el blog hay una serie de notas sobre buenas prácticas en Excel (puedes hacer una búsqueda en el blog con la palabra "prácticas").
UN BUEN BLOG, LO FELICITO, SOY UN JOVEN QUE TENGO 16 AÑOS ESTOY EN EL GRADO ONCE DE BACHILLERATO, DESDE NATAGAIMA TOLIMA... SI ME HA SERVIDO TODAS SUS VENTANAS ME SIRVEN UN BUEN APOYO DE TAREAS Y SIGUEN LUCHANDO POR SER MEJORES.. YA TODAS LAS TAREAS QUE HAGO EN SU BLOG ME HE SACADO BUEN PUNTUAJE... Y LOS FELICITO... GRACIAS POR AYUDARME MUCHO...
ResponderBorrarexcelentes articulos
ResponderBorrarHola, excelente artículo, muchas gracias!
ResponderBorrarTengo la siguiente expresión:
=SUMA(CONTAR.SI(INDIRECTO({"I50","K50","M50","O50","Q50","S50","K50","U50","W50","K50","Y50","AA50","AC50","AE50"}),">0"))
Y requiero correr la fórmula hacia abajo para que realice el conteo en las demás filas, osea que se actualice a 51,52,53,54, pero al ser una matriz en base a los datos de los rangos expresados como texto no me lo permite.
Agradecería si alguien tiene algún tip para realizar esta operación.
Gracias!
Ana.
Ana, podrías usar esta forma
ResponderBorrar=SUMA(CONTAR.SI(INDIRECTO({"I"&FILA(),"K"&FILA(),"M"&FILA()....}),">0"))
La función FILA() da como resultado el número de fila de la celda que contiene la fórmula, de manera que al arrastrar la fórmula se convertiría en 51, 52, etc.