CONTAR.SI en varias hojas no funciona con SUMPRODUCTO

Question

Tengo las dos hojas a continuación.

(La tabla de ejemplo anterior está muy simplificada para esta pregunta)

Estoy intentando escribir una fórmula que sume el total de apariciones de A en el año 2017 (la respuesta es 3).

Esta fórmula funciona si sumo dos CONTAR.SI para cada hoja:

=COUNTIF(INDEX(Sheet2!A2:C4, 0, MATCH(2017, Sheet2!A2:C2, 0)), "A") + COUNTIF(INDEX(Sheet3!A2:C4, 0, MATCH(2017, Sheet3!A2:C2, 0)), "A")

Intenté combinarlos en una sola fórmula a continuación. Sin embargo, recibo un error #Value. "Hojas" es un rango con nombre que consta de {Hoja2, Hoja3}.

=SUMPRODUCT(COUNTIF(INDEX(INDIRECT("'"&Sheets&"'!"&"A2:C4"), 0, MATCH(2017, INDIRECT("'"&Sheets&"'!"&"A2:C2"), 0)), "A"))

¿Por qué la fórmula anterior me da un error cuando intento utilizar el rango con nombre?

Answer 1

Reproduje con éxito la primera fórmula entre hojas y está funcionando, pero SUMPRODUCTnecesita poca corrección y debe escribirse así, y obtendrá el resultado correcto.3:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheets&"'!A3:C4"),"A")-COUNTIF(INDIRECT("'"&Sheets&"'!A2:C2"),"2017"))

NÓTESE BIEN

• ¿Dónde Sheetsestá el rango nombrado? El nombre de la hoja está involucrado con la fórmula utilizada.

• En la fórmula anterior, primero encuentra el número de celdas que tienen un valor Ay luego resta el 5recuento de celdas con un valor 2017, 2luego la fórmula devuelve 3....(5 As - 2 2017s) = 3

• Si usa +el signo que generalmente se usa con múltiples COUNTIF, entonces en su caso obtendrá 7, y si CONTAR.SI está separado por ,la fórmula, devuelve5

:Editado:

Dado que a veces la fórmula que se muestra arriba no funciona correctamente, me gustaría sugerir esta fórmula de matriz (CSE):

{=SUM(IF(ISNUMBER(Sheet1!$A$170:$C$170),IF(Sheet1!$A$170:$C$170=2017,IF(Sheet1!$A$171:$C$172="A",1))))+SUM(IF(ISNUMBER(Sheet2!$A$170:$C$170),IF(Sheet2!$A$170:$C$170=2017,IF(Sheet2!$A$171:$C$172="A",1))))}

• Terminarlo conCtrl+Mayús+Entrar