Generisk formel
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",rng1)) + ISNUMBER(SEARCH("rat",rng1)))>0),rng2)
Resumé
For at opsummere, hvis celler indeholder enten en tekststreng eller en anden (dvs. indeholder "kat" eller "rotte"), kan du bruge funktionen SUMPRODUCT sammen med ISNUMBER + SEARCH eller FIND. I det viste eksempel er formlen i celle F5:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8)) + ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
som returnerer summen af værdierne i C4: C8, når celler i B4: B8 indeholder enten "kat" eller "rotte".
Forklaring
Når du summerer celler med "ELLER" -kriterier, skal du være forsigtig med ikke at tælle dobbelt, når der er en mulighed for, at begge kriterier vender tilbage til sande. I det viste eksempel vil vi sammenfatte værdier i kolonne C, når celler i kolonne B indeholder enten "kat" eller "rotte". Vi kan ikke bruge SUMIF'er med to kriterier, fordi SUMIFS er baseret på OG-logik. Og hvis vi prøver at bruge to SUMIFS (dvs. SUMIFS + SUMIFS), tæller vi dobbelt, fordi der er celler, der indeholder både "kat" og "rotte".
I stedet bruger vi en formel som denne:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8))+ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
Denne kerne af denne formel er baseret på formlen forklaret her, der lokaliserer tekst inde i en celle med ISNUMBER og SEARCH:
ISNUMBER(SEARCH("text",range)
Når der gives en række celler, returnerer dette uddrag en matrix med SAND / FALSK-værdier, en værdi for hver celle i området. I denne formel bruger vi dette uddrag to gange, en gang til "kat" og en gang til "rotte", så vi får to arrays. På dette tidspunkt har vi:
=SUMPRODUCT(--(((TRUE;FALSE;TRUE;FALSE;FALSE)+ (TRUE;FALSE;TRUE;TRUE;FALSE))>0),C4:C8)
Dernæst tilføjer vi disse arrays sammen, fordi tilføjelse bruges i boolsk algebra til ELLER-logik. Matematikoperationen tvinger automatisk de SANDE og FALSKE værdier til 1s og 0s, så vi ender med arrayet nedenfor:
=SUMPRODUCT(--(((2;0;2;1;0))>0),C4:C8)
Hvert nummer i denne matrix er resultatet af at tilføje de SANDE og FALSKE værdier i de oprindelige to arrays sammen. I det viste eksempel ser matrixen sådan ud:
(2;0;2;1;0)
Vi er nødt til at tilføje disse tal, men vi vil ikke fordoble antallet. Så vi er nødt til at sikre, at enhver værdi større end nul bare tælles en gang. For at gøre det tvinger vi alle værdier til SAND eller FALSK ved at kontrollere arrayet med "> 0". Dette returnerer SAND / FALSK:
=SUMPRODUCT(--((TRUE;FALSE;TRUE;TRUE;FALSE)),C4:C8)
Som vi derefter konverterer til 1/0 ved hjælp af en dobbelt negativ (-):
=SUMPRODUCT((1;0;1;1;0),C4:C8)
og endelig:
=SUMPRODUCT((1;0;1;1;0),(20;15;30;20;10))
SUMPRODUCT multiplicerer de tilsvarende elementer i de to arrays sammen og summerer resultatet og returnerer 70.
Skift mellem store og små bogstaver
SEARCH-funktionen ignorerer store og små bogstaver. Hvis du har brug for en følsom indstilling, skal du udskifte SEARCH med FIND-funktionen.
