Üldine valem
=SUMPRODUCT(--(COUNTIF(list1,list2)=0))
Kokkuvõte
Ühes loendis olevate väärtuste loendamiseks, mis puuduvad teisest loendist, võite kasutada funktsioone COUNTIF ja SUMPRODUCT põhinevat valemit.
Näidatud näites on H6 valem:
=SUMPRODUCT(--(COUNTIF(list1,list2)=0))
Mis tagastab 1, kuna väärtust "Osborne" ei kuvata lahtris B6: B11.
Selgitus
Funktsioonid COUNTIF kontrollib vahemikus olevaid väärtusi kriteeriumidega. Sageli esitatakse ainult üks kriteerium, kuid sel juhul esitame rohkem kui ühe kriteeriumi.
Vahemiku jaoks anname COUNTIF- ile nimega vahemiku loendi1 (B6: B11) ja kriteeriumide jaoks nimega vahemiku loendi2 (F6: F8).
Kuna anname COUNTIF-ile rohkem kui ühe kriteeriumi, saame tulemuste massiivi korral mitu, mis näeb välja järgmine: (2; 1; 0)
Me tahame loendada ainult puuduvaid väärtusi, millel on definitsiooni järgi null väärtus, nii et teisendame need väärtused väärtuseks TRUE ja FALSE lausega "= 0", mis annab: (FALSE; FALSE; TRUE)
Seejärel sunnime topeltnegatiivse operaatoriga (-) TRUE FALSE väärtused väärtusele 1s ja 0s, mis annab: (0; 0; 1)
Lõpuks kasutame massiivi üksuste liitmiseks ja puuduvate väärtuste koguarvu tagastamiseks SUMPRODUCTi.
Alternatiiv MATCH-iga
Kui eelistate sõnasõnalisemaid valemeid, võite kasutada allpool toodud valemit, mis põhineb MATCHil, mis loendab ISNA funktsiooni abil sõna otseses mõttes puuduvaid väärtusi:
=SUMPRODUCT(--ISNA(MATCH(list2,list1,0)))
