Üldine valem
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",rng1)) + ISNUMBER(SEARCH("rat",rng1)))>0),rng2)
Kokkuvõte
Kokkuvõtteks, kas lahtrid sisaldavad ühte või teist tekstistringi (st sisaldavad "kass" või "rott"), saate funktsiooni SUMPRODUCT kasutada koos funktsiooniga ISNUMBER + SEARCH või LEIDA. Näidatud näites on lahtris F5 valem:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8)) + ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
mis tagastab C4: C8 väärtuste summa, kui lahtrites B4: B8 on kas "cat" või "rot".
Selgitus
"OR" kriteeriumitega lahtrite summeerimisel peate olema ettevaatlik, et mitte topeltloendada, kui on võimalus, et mõlemad kriteeriumid vastavad tõele. Näidatud näites tahame veerus C olevad väärtused kokku võtta, kui veeru B lahtrid sisaldavad kas "kass" või "rott". Me ei saa kasutada SUMIF-e kahe kriteeriumiga, kuna SUMIFS põhineb loogikal AND. Ja kui proovime kasutada kahte SUMIF-i (st SUMIFS + SUMIFS), loendame topelt, sest on rakke, mis sisaldavad nii "cat" kui "rot".
Selle asemel kasutame sellist valemit:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8))+ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
Selle valemi põhituum põhineb siin selgitatud valemil, mis otsib lahtri sees teksti ISNUMBER ja SEARCH:
ISNUMBER(SEARCH("text",range)
Lahtrivahemiku määramisel tagastab see koodilõik TRUE / FALSE väärtuste massiivi, üks väärtus iga vahemiku lahtrite kohta. Selles valemis kasutame seda juppi kaks korda, üks kord "kass" ja teine "rott", nii et saame kaks massiivi. Sel hetkel on meil:
=SUMPRODUCT(--(((TRUE;FALSE;TRUE;FALSE;FALSE)+ (TRUE;FALSE;TRUE;TRUE;FALSE))>0),C4:C8)
Järgmisena lisame need massiivid kokku, sest liitmist kasutatakse loogika algebras OR-loogika jaoks. Matemaatikaoperatsioon sunnib TÕENE ja VALE väärtused automaatselt väärtusele 1 ja 0, nii et jõuame lõpuks allpool oleva massiivini:
=SUMPRODUCT(--(((2;0;2;1;0))>0),C4:C8)
Selle massiivi iga number tuleneb kahe algse massiivi TRUE ja FALSE väärtuste liitmisest. Näidatud näites näeb massiiv välja selline:
(2;0;2;1;0)
Peame need arvud kokku liitma, kuid me ei taha topeltarvestust teha. Seega peame veenduma, et kõik nullist suuremad väärtused loetakse vaid üks kord. Selleks sunnime kõik väärtused väärtusele TÕENE või VALE, kontrollides massiivi nupuga "> 0". See tagastab TÕENE / VALE:
=SUMPRODUCT(--((TRUE;FALSE;TRUE;TRUE;FALSE)),C4:C8)
Mille teisendame seejärel kahekordse negatiivse (-) abil 1/0-ks:
=SUMPRODUCT((1;0;1;1;0),C4:C8)
ja lõpuks:
=SUMPRODUCT((1;0;1;1;0),(20;15;30;20;10))
SUMPRODUCT korrutab kahe massiivi vastavad elemendid kokku ja liidab tulemuse, tagastades 70.
Tõstutundlik valik
Funktsioon SEARCH ignoreerib väiketähti. Kui vajate tundlikku valikut, asendage SEARCH funktsiooniga LEIA.
