Generisk formel
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",rng1)) + ISNUMBER(SEARCH("rat",rng1)))>0),rng2)
Sammendrag
For å oppsummere om celler inneholder enten en tekststreng eller en annen (dvs. inneholder "cat" eller "rat"), kan du bruke SUMPRODUCT-funksjonen sammen med ISNUMBER + SØK eller FINN. I eksemplet vist er formelen i celle F5:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8)) + ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
som returnerer summen av verdiene i C4: C8 når celler i B4: B8 inneholder enten "cat" eller "rotte".
Forklaring
Når du summerer celler med "ELLER" -kriterier, må du være forsiktig så du ikke teller dobbelt når det er en mulighet for at begge kriteriene vil være sanne. I eksemplet som vises, vil vi summere verdier i kolonne C når celler i kolonne B inneholder enten "cat" eller "rotte". Vi kan ikke bruke SUMIF-er med to kriterier, fordi SUMIFS er basert på OG-logikk. Og hvis vi prøver å bruke to SUMMER (dvs. SUMMER + SUMMER) vil vi dobbelt telle fordi det er celler som inneholder både "katt" og "rotte".
I stedet bruker vi en formel som denne:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8))+ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
Denne kjernen i denne formelen er basert på formelen forklart her som lokaliserer tekst inne i en celle med ISNUMBER og SØK:
ISNUMBER(SEARCH("text",range)
Når du får en rekke celler, returnerer denne kodebiten en matrise med SANNE / FALSE verdier, en verdi for hver celle i området. I denne formelen bruker vi dette utdraget to ganger, en gang for "katt" og en gang for "rotte", så vi får to matriser. På dette punktet har vi:
=SUMPRODUCT(--(((TRUE;FALSE;TRUE;FALSE;FALSE)+ (TRUE;FALSE;TRUE;TRUE;FALSE))>0),C4:C8)
Deretter legger vi disse gruppene sammen fordi tillegg brukes i boolsk algebra for ELLER-logikk. Matematikkoperasjonen tvinger automatisk de SANNE og FALSE verdiene til 1s og 0s, så vi ender opp med matrisen nedenfor:
=SUMPRODUCT(--(((2;0;2;1;0))>0),C4:C8)
Hvert tall i denne matrisen er resultatet av å legge til SANNE og FALSE verdier i de to opprinnelige gruppene sammen. I eksemplet som vises ser matrisen slik ut:
(2;0;2;1;0)
Vi må legge til disse tallene, men vi vil ikke doble teller. Så vi må sørge for at en verdi større enn null bare telles en gang. For å gjøre det tvinger vi alle verdiene til SANT eller FALSK ved å sjekke matrisen med "> 0". Dette returnerer SANN / FALSK:
=SUMPRODUCT(--((TRUE;FALSE;TRUE;TRUE;FALSE)),C4:C8)
Som vi deretter konverterer til 1/0 ved hjelp av en dobbel negativ (-):
=SUMPRODUCT((1;0;1;1;0),C4:C8)
og endelig:
=SUMPRODUCT((1;0;1;1;0),(20;15;30;20;10))
SUMPRODUCT multipliserer tilsvarende elementer av de to matriser sammen og summerer resultatet, og returnerer 70.
Saksfølsomt alternativ
SØK-funksjonen ignorerer tilfelle. Hvis du trenger et sensitivt alternativ, bytter du SØK ut med FINN-funksjonen.
