Obecný vzorec
=SUMPRODUCT(COUNTIF(INDIRECT("'"&sheets&"'!"&rng),criteria))
souhrn
Chcete-li počítat shody v celém sešitu, můžete použít vzorec založený na funkcích COUNTIF a SUMPRODUCT. V zobrazeném příkladu je vzorec v D5:
=SUMPRODUCT(COUNTIF(INDIRECT("'"&sheets&"'!A1:Z10000"),B5))
kde "listy" je pojmenovaný rozsah B8: B10.
Kontext
Tento sešit má čtyři listy. Tři z pracovních listů „List1“, „List2“ a „List3“ obsahují 1000 náhodných křestních jmen v tabulce, která vypadá takto:
Poznámka: rozsah, který používáme ve vzorci, A1: Z10000, je libovolný a měl by být upraven tak, aby vyhovoval vašim údajům.
Vysvětlení
Při práci zevnitř ven máme nejprve tento výraz uvnitř funkce INDIRECT:
"'"&sheets&"'!A1:Z10000"
Protože "sheets" je pojmenovaný rozsah, který obsahuje "Sheet1", "Sheet2" a "Sheet3", po vyhodnocení výrazu získáme takové pole:
("'Sheet1'!A1:Z10000";"'Sheet2'!A1:Z10000";"'Sheet3'!A1:Z10000")
Uvnitř pole máme tři hodnoty a každá z nich je název listu připojený zřetězením do rozsahu A1: Z10000. Všimněte si, že se jedná o všechny textové hodnoty.
Dále se funkce INDIRECT používá k převodu každé textové hodnoty na správný odkaz, který je dodáván funkci COUNTIF jako argument rozsahu, spolu s hodnotou v D5 pro kritéria.
Protože jsme dali COUNTIF tři samostatné rozsahy, dostaneme zpět tři výsledky v poli takto:
(5;6;5)
Každá položka se počítá pro jeden list.
Nakonec se SUMPRODUCT použije k sečtení pole a vrátí výsledek 16.
