Generisk formel
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",rng1)) + ISNUMBER(SEARCH("rat",rng1)))>0),rng2)
Sammanfattning
För att summera om celler innehåller antingen en textsträng eller en annan (dvs. innehåller "cat" eller "rat") kan du använda SUMPRODUCT-funktionen tillsammans med ISNUMBER + SÖK eller HITTA. I exemplet som visas är formeln i cell F5:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8)) + ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
som returnerar summan värdena i C4: C8 när celler i B4: B8 innehåller antingen "katt" eller "råtta".
Förklaring
När du summerar celler med "ELLER" -kriterier måste du vara försiktig så att du inte räknar dubbelt när det finns en möjlighet att båda kriterierna kommer att vara sanna. I exemplet som visas vill vi summera värden i kolumn C när celler i kolumn B innehåller antingen "katt" eller "råtta". Vi kan inte använda SUMIF med två kriterier, eftersom SUMIFS är baserat på OCH-logik. Och om vi försöker använda två SUMIFS (dvs. SUMIFS + SUMIFS) kommer vi att räkna dubbelt eftersom det finns celler som innehåller både "katt" och "råtta".
Istället använder vi en sådan formel:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8))+ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
Denna kärna i denna formel bygger på formeln som förklaras här som lokaliserar text inuti en cell med ISNUMBER och SÖK:
ISNUMBER(SEARCH("text",range)
När ett cellintervall ges kommer detta kodavsnitt att returnera en matris med SANT / FALSKA värden, ett värde för varje cell i intervallet. I den här formeln använder vi detta utdrag två gånger, en gång för "katt" och en gång för "råtta", så vi får två matriser. Vid denna tidpunkt har vi:
=SUMPRODUCT(--(((TRUE;FALSE;TRUE;FALSE;FALSE)+ (TRUE;FALSE;TRUE;TRUE;FALSE))>0),C4:C8)
Därefter lägger vi till dessa matriser tillsammans eftersom addition används i boolesk algebra för ELLER-logik. Matematikoperationen tvingar automatiskt de sanna och falska värdena till 1s och 0s, så vi slutar med arrayen nedan:
=SUMPRODUCT(--(((2;0;2;1;0))>0),C4:C8)
Varje nummer i denna matris är resultatet av att man sätter SANT och FALSKA värden i de två ursprungliga grupperna tillsammans. I exemplet som visas ser matrisen ut så här:
(2;0;2;1;0)
Vi måste lägga till dessa siffror, men vi vill inte räkna dubbelt. Så vi måste se till att något värde större än noll bara räknas en gång. För att göra det tvingar vi alla värden till SANT eller FALSK genom att markera matrisen med "> 0". Detta returnerar SANT / FALSKT:
=SUMPRODUCT(--((TRUE;FALSE;TRUE;TRUE;FALSE)),C4:C8)
Som vi sedan konverterar till 1/0 med hjälp av en dubbel negativ (-):
=SUMPRODUCT((1;0;1;1;0),C4:C8)
och slutligen:
=SUMPRODUCT((1;0;1;1;0),(20;15;30;20;10))
SUMPRODUCT multiplicerar motsvarande element i de två matriserna tillsammans och summerar resultatet och returnerar 70.
Skiftlägeskänsligt alternativ
SÖK-funktionen ignorerar fall. Om du behöver ett känsligt alternativ, ersätt SÖK med funktionen HITTA.
