Obecný vzorec
=SUMPRODUCT(--(COUNTIF(list1,list2)=0))
souhrn
Chcete-li spočítat hodnoty v jednom seznamu, které chybí v jiném seznamu, můžete použít vzorec založený na funkcích COUNTIF a SUMPRODUCT.
V zobrazeném příkladu je vzorec v H6:
=SUMPRODUCT(--(COUNTIF(list1,list2)=0))
Který vrací 1, protože hodnota „Osborne“ se v B6: B11 neobjevuje.
Vysvětlení
Funkce COUNTIF kontrolují hodnoty v rozsahu podle kritérií. Často se dodává pouze jedno kritérium, ale v tomto případě dodáváme více než jedno kritérium.
Pro rozsah dáme COUNTIF pojmenovaný seznam rozsahu1 (B6: B11) a pro kritéria poskytneme pojmenovaný rozsah seznamu2 (F6: F8).
Protože dáme COUNTIF více než jedno kritérium, získáme více než jeden výsledek v poli výsledků, které vypadá takto: (2; 1; 0)
Chceme spočítat pouze chybějící hodnoty, které mají podle definice počet nula, proto tyto hodnoty převedeme na TRUE a FALSE pomocí příkazu "= 0", který získá: (FALSE; FALSE; TRUE)
Potom vynutíme hodnoty TRUE FALSE na 1 s a 0 s dvojitým záporným operátorem (-), který vytvoří: (0; 0; 1)
Nakonec použijeme SUMPRODUCT k přidání položek do pole a vrácení celkového počtu chybějících hodnot.
Alternativa k MATCH
Pokud dáváte přednost více doslovným vzorcům, můžete použít vzorec níže založený na MATCH, který pomocí funkce ISNA doslova spočítá hodnoty, které „chybí“:
=SUMPRODUCT(--ISNA(MATCH(list2,list1,0)))
