Función SUMAR.SI con referencia 3D en Excel

Parece complicado pero no lo es (tanto).

«‘» name_range_of_sheet_names «‘»: * Es un enlace: / excel-range-name-all-about-excel-named-ranges-excel-range-name [named range] que contiene los nombres de las hojas. Esto es muy importante.

«rango_criterios»: * Es la referencia de texto de los criterios que contienen rango. (Debería ser el mismo en todas las hojas para el trabajo de referencia en 3D.)

Criterios: * Es simplemente la condición que desea poner para la suma. Puede ser un texto o una referencia de celda.

«rango_suma»: * Es la referencia de texto del rango de suma. (Debería ser el mismo en todas las hojas para el trabajo de referencia en 3D.)

Suficiente de teoría, vamos a hacer referencia en 3D con la función SUMIF funcionando. === Ejemplo: Suma por región de varias hojas usando referencias 3D de Excel: *

Estamos tomando los mismos datos que tomamos en el link: / excel-generals-3d-reference-in-excel [ejemplo de referencia simple en 3D]. En este ejemplo, tengo cinco hojas diferentes que contienen datos similares. Cada hoja contiene los datos de un mes. En la hoja maestra, quiero la suma de unidades y la colección por región de todas las hojas. Hagámoslo primero con las Unidades. Las unidades están en el rango D2: D14 en todas las hojas.

Ahora, si usa la referencia 3D normal con la función SUMIF, = SUMIF (Jan: Apr! A2: A14, Master! B4, Jan: Apr! D2: D14)

Devolverá # ¡VALOR! error. Entonces no podemos usarlo. Usaremos la fórmula genérica mencionada anteriormente.

Usando la fórmula SUMIF genérica de referencia 3D anterior de Excel, escriba esta fórmula en la celda C3:

Aquí, meses es un enlace: / excel-range-name-all-about-excel-named-ranges-excel-range-name [named range] que contiene los nombres de las hojas. Esto es crucial.

Cuando presiona enter, obtiene su salida exacta.

How does it work? The core of the formula is the INDIRECT function and the named range. Here the string»‘»&Months&»‘!» & «A2:A14″translates to an array of range references of each sheet in the named range.*

Esta matriz contiene la referencia de texto * de los rangos, no los rangos reales. Ahora que es una referencia de texto, la función INDIRECTO puede usarla para convertirlos en rangos reales. Esto sucede para ambas funciones INDIRECTAS. Después de resolver los textos dentro de las funciones INDIRECTAS (mantén presionado), la fórmula se ve así:

Ahora entra en acción la función SUMIF (no la INDIRECTA, como habrás adivinado). La condición se corresponde con el primer rango «‘Jan’! A2: A14». Aquí, la función INDIRECTO funciona dinámicamente y convierte este texto en el rango real (_ por eso, si primero intenta resolver INDIRECTO usando la tecla F9, no obtendrá el resultado_). A continuación, suma los valores coincidentes en el rango «‘Jan’! D2: D14». * Esto sucede para cada rango en la matriz. Finalmente, tendremos una matriz devuelta por la función SUMIF.

Ahora SUMPRODUCT hace lo que mejor sabe hacer. Suma estos valores y conseguimos que nuestra función 3D SUMIF funcione.

Así que sí, chicos, así es como pueden lograr una función SUMIF 3D. Esto es un poco complejo, estoy de acuerdo en eso. Hay mucho margen para errores en esta fórmula 3D. Le sugiero que use la función SUMIF en cada hoja en una celda definida y luego use el link: / excel-generals-3d-reference-in-excel [referencia 3D normal] para resumir esos valores.

Espero haber sido lo suficientemente explicativo. Si tiene alguna duda sobre la referencia de Excel a cualquier otra consulta relacionada con Excel / VBA, pregunte en la sección de comentarios a continuación.

enlace: / excel-generals-relativa-y-absoluta-referencia-en-excel [Referencia relativa y absoluta en Excel] | Hacer referencia en Excel es un tema importante para todos los principiantes. Incluso los usuarios de Excel experimentados cometen errores al hacer referencia.

link: / lookup-formulas-dynamic-worksheet-reference [Dynamic Worksheet Reference] | Proporcione hojas de referencia dinámicamente utilizando la función INDIRECTA de excel. Esto es simple …​

link: / excel-range-name-expansion-reference-in-excel [Expandiendo las referencias en Excel] | La referencia en expansión se expande cuando se copia hacia abajo o hacia la derecha. Usamos el signo $ antes de la columna y el número de fila para hacerlo. Aquí hay un ejemplo …​

link: / excel-range-name-absolute-reference-in-excel [Todo sobre la referencia absoluta] | El tipo de referencia predeterminado en Excel es relativo, pero si desea que la referencia de celdas y rangos sea absoluta, use el signo $. Aquí están todos los aspectos de la referencia absoluta en Excel.

link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-aumentar-su-productividad [50 atajos de Excel para aumentar su productividad] | Acelera tu tarea. Estos 50 atajos lo harán trabajar aún más rápido en Excel.

enlace: / fórmulas-y-funciones-introducción-de-función-vlookup [La función BUSCARV en Excel] | Esta es una de las funciones más utilizadas y populares de Excel que se utiliza para buscar valores de diferentes rangos y hojas.

enlace: / tips-countif-in-microsoft-excel [COUNTIF en Excel 2016] | Cuente valores con condiciones usando esta asombrosa función. No necesita filtrar sus datos para contar un valor específico.

La función Countif es esencial para preparar su tablero.

link: / excel-formula-and-function-excel-sumif-function [Cómo utilizar la función SUMIF en Excel] | Esta es otra función esencial del tablero. Esto le ayuda a resumir valores en condiciones específicas.