Note: The other languages of the website are Google-translated. Back to English
English English

Countifs med ELLER -logik för flera kriterier i Excel

Normalt kan du använda COUNTIFS-funktionen för att räkna celler baserat på ett eller flera villkor med OCH-logik i Excel. Har du någonsin råkat ut för en situation där du behöver räkna mer än ett värde från en enda kolumn eller ett cellintervall? Detta innebär att räkna med flera villkor och ELLER-logik. I det här fallet kan du använda funktionerna SUMMA och COUNTIFS tillsammans, eller använda SUMMAPRODUKT-funktionen.


Räkna celler med ELLER-villkor i Excel

Till exempel har jag en rad data som visas nedan, nu vill jag räkna numret på produkten som är "Penna" eller "Linjal", här kommer jag att diskutera två formler för att lösa denna uppgift i Excel.

Räkna celler med ELLER-villkor genom att använda SUM- och COUNTIFS-funktionerna

I Excel, för att räkna med flera ELLER-villkor, kan du använda funktionerna SUM och COUNTIFS med en arraykonstant, den generiska syntaxen är:

=SUM(COUNTIF(range, {criterion1, criterion2, criterion3, …}))
  • range: Dataintervallet innehåller kriterierna där du räknar celler från;
  • criterion1, criterion2, criterion3…: Villkoren du vill räkna celler baserat på.

För att räkna antalet produkter som är "Penna" eller "Linjal", kopiera eller skriv in formeln nedan i en tom cell och tryck sedan på ange nyckel för att få resultatet:

=SUM(COUNTIFS(B2:B13,{"Pencil","Ruler"}))


Förklaring av formeln:

=SUMMA(ANTAL.OM(B2:B13;{"Penna","Linjal"}))

  • {"Pencil","Linjal"}: Först bör du paketera alla villkor i en arraykonstant så här: {"Pencil","Ruler"}, separera objekten med kommatecken.
  • COUNTIFS(B2:B13;{"Penna","Linjal"}): Den här COUNTIFS-funktionen kommer att få individuella räkningar för "Penna" och "Linjal", och du får resultatet så här: {2,3}.
  • SUMMA(COUNTIFS(B2:B13;{"Pencil","Linjal"}))=SUMMA({2,3}): Slutligen summerar denna SUM-funktion alla objekt i arrayen och returnerar resultatet: 5.

tips: Du kan också använda cellreferenser för kriterier, använd nedanstående matrisformel och tryck sedan på Ctrl + Skift + Enter nycklar tillsammans för att få rätt resultat:

=SUM(COUNTIF(B2:B13,D2:D3))


Räkna celler med ELLER-villkor genom att använda SUMPRODUCT-funktionen

Här är en annan formel som skapas av SUMPRODUCT-funktionen som kan hjälpa till att räkna celler med OR-logik också. Den generiska syntaxen är:

=SUMPRODUCT(1*(range ={criterion1, criterion2, criterion3, …}))
  • range: Dataintervallet innehåller kriterierna där du räknar celler från;
  • criterion1, criterion2, criterion3…: Villkoren du vill räkna celler baserat på.

Kopiera eller skriv in följande formel i en tom cell och tryck ange nyckel för att returnera resultatet:

=SUMPRODUCT(1*(B2:B13={"Pencil","Ruler"}))


Förklaring av formeln:

=SUMMAPRODUKT(1*(B2:B13={"Penna","Linjal"}))

  • B2:B13={"Pencil","Linjal"}: Detta uttryck jämför varje kriterium "Penna", "Linjal" med områdescell B2:B13. Om kriteriet är uppfyllt, returnerar det en TRUE, annars visas en FALSE, du får resultatet så här: {TRUE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,TRUE;FALSE,FALSE;TRUE,FALSE ;FALSE,FALSE;FALSE,TRUE;FALSE,FALSE;FALSE,FALSE;FALSE,TRUE;FALSE,FALSE}.
  • 1*(B2:B13={"Pencil","Linjal"}): Multiplikation konverterar de logiska värdena – TRUE och FALSE till 1 och 0, så det kommer att få resultatet så här: {1,0;0,0;0,0;0,1;0,0;1,0;0,0 ,0,1;0,0;0,0;0,1;0,0;XNUMX}.
  • SUMPRODUCT(1*(B2:B13={"Pencil","Ruler"}))= SUMPRODUCT({1,0;0,0;0,0;0,1;0,0;1,0;0,0;0,1;0,0;0,0;0,1;0,0}): Äntligen lägger SUMPRODUCT-funktionen ihop alla siffror i arrayen för att få resultatet: 5.

Räkna celler med flera uppsättningar av ELLER-villkor i Excel

Ibland kan du behöva räkna cellerna med två eller flera uppsättningar av OR-villkor, i det här fallet kan du använda antingen SUM och COUNTIFS med en arraykonstant eller SUMPRODUCT med ISNUMBER MATCH-funktioner.

Räkna celler med två uppsättningar ELLER-villkor genom att använda SUM- och COUNTIFS-funktionerna

För att bara hantera två uppsättningar av ELLER-kriterier behöver du bara lägga till ytterligare en arraykonstant till COUNTIFS-formeln.

Till exempel, jag har dataintervallet som visas nedan, nu vill jag räkna personerna som beställde "Penna" eller "Linjal" och beloppet är antingen <100 eller >200.

Ange eller kopiera följande formel till en cell och tryck sedan på ange nyckel för att få resultatet, se skärmdump:

=SUM(COUNTIFS(B2:B13,{"Pencil","Ruler"},C2:C13,{"<100";">200"}))

Anmärkningar: I formeln ska du använda semikolon för den andra arraykonstanten, vilket skapar en vertikal array.


Räkna celler med flera uppsättningar av ELLER-villkor genom att använda SUMPRODUCT-funktionen

Ovanstående formel fungerar bara för två uppsättningar av ELLER-kriterier, om du behöver räkna med fler kriterier kan en komplex SUMPRODUKT-formel tillsammans med ISNUMBER MATCH-funktioner hjälpa dig.

Ta till exempel uppgifterna nedan, för att räkna personerna som beställde "Penna" eller "Linjal" och statusen är antingen "Levererad" eller "I transit" och undertecknad av "Bob" eller "Eko", bör du tillämpa en komplex formel .

Kopiera eller skriv in formeln nedan i en tom cell och tryck sedan på ange nyckel för att få beräkningen, se skärmdump:

=SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))*ISNUMBER(MATCH(C2:C13,{"Delivered","In transit"},0))*ISNUMBER(MATCH(D2:D13,{"Bob","Eko"},0)))


Förklaring av formeln:

=SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))*ISNUMBER(MATCH(C2:C13,{"Delivered","In transit"},0))*ISNUMBER(MATCH(D2:D13,{"Bob","Eko"},0)))

ISNUMMER(MATCH(B2:B13;{"Pencil","Linjal"},0)):

  • MATCH(B2:B13;{"Penna","Linjal"},0): Denna MATCH-funktion används för att jämföra varje cell i området B2:B13 med motsvarande arraykonstant. Om matchningen hittas returnerar den en relativ position för värdet i matrisen, annars visas ett felvärde. Så, du kommer att få arraylistan så här: {1;#N/A;#N/A;2;#N/A;1;#N/A;2;1;#N/A;2;# N/A}.
  • ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))= ISNUMBER({1;#N/A;#N/A;2;#N/A;1;#N/A;2;1;#N/A;2;#N/A}):ISNUMBER-funktionen omvandlar siffrorna till TRUEs och felvärden till FALSEs som detta: {TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE}.

Ovanstående logik kan också tillämpas på de andra och tredje ISNUMBER-uttrycken.

SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))*ISNUMBER(MATCH(C2:C13,{"Delivered","In transit"},0))*ISNUMBER(MATCH(D2:D13,{"Bob","Eko"},0))):

  • Sedan multipliceras dessa tre arrayer tillsammans inuti SUMPRODUKT, som automatiskt omvandlar SANT och FALSK värden till 1:or och 0:or som en del av den matematiska operationen så här: SUMPRODUKT({1;0;0;1;0;1;0;1; 1;0;1;0}*{1;1;0;0;1;1;0;1;1;0;1;1} *{1;0;0;0;0;0;0; 1;0;0;1;0})=SUMMAPRODUKT({1;0;0;0;0;0;0;1;0;0;1;0}).
  • Till sist summerar funktionen SUMMAPRODUKT alla siffror i arrayen för att få resultatet: 3.

Relativ funktion som används:

  • BELOPP:
  • Excel SUMM -funktionen returnerar summan av angivna värden.
  • RÄKNING:
  • COUNTIF-funktionen är en statistisk funktion i Excel som används för att räkna antalet celler som uppfyller ett kriterium.
  • SUMMAPRODUKT:
  • SUMPRODUCT-funktionen kan användas för att multiplicera två eller flera kolumner eller matriser tillsammans och sedan få summan av produkter.
  • ISNUMBER:
  • Excel ISNUMBER -funktionen returnerar TRUE när en cell innehåller ett tal och FALSE om inte.
  • MATCH:
  • Microsoft Excel MATCH-funktionen söker efter ett specifikt värde i ett cellområde och returnerar den relativa positionen för detta värde.

Fler artiklar:

  • Räkna unika numeriska värden baserade på kriterier
  • I Excel -kalkylblad kan du drabbas av ett problem som räknar antalet unika numeriska värden baserat på ett specifikt villkor. Till exempel, hur kan jag räkna de unika Qty-värdena för produkten ”T-shirt” från rapporten enligt nedanstående skärmdump? I den här artikeln kommer jag att visa några formler för att uppnå denna uppgift i Excel.
  • Räkna antal rader med flera eller kriterier
  • För att räkna antalet rader med flera kriterier i olika kolumner, med ELLER -logik, kan SUMPRODUCT -funktionen hjälpa dig. Till exempel har jag en produktrapport enligt nedanstående skärmdump, nu vill jag räkna raderna där produkten är "T-shirt" eller färgen är "Svart". Hur hanterar jag den här uppgiften i Excel?

De bästa Office-produktivitetsverktygen

Kutools för Excel - Hjälper dig att sticka ut från mängden

Vill du slutföra ditt dagliga arbete snabbt och perfekt? Kutools för Excel ger 300 kraftfulla avancerade funktioner (kombinera arbetsböcker, summera efter färg, dela cellinnehåll, konvertera datum och så vidare ...) och spara 80% tid åt dig.

  • Designad för 1500 arbetsscenarier, hjälper dig att lösa 80% Excel-problem.
  • Minska tusentals tangentbord och musklick varje dag, lindra dina trötta ögon och händer.
  • Bli en expert på 3 minuter. Behöver inte längre komma ihåg några smärtsamma formler och VBA-koder.
  • 30 dagars obegränsad gratis provperiod. 60-dagars pengarna tillbaka-garanti. Gratis uppgradering och support i 2 år.
Ribbon of Excel (med Kutools för Excel installerat)

Fliken Office - Aktivera läsning och redigering av flikar i Microsoft Office (inkluderar Excel)

  • En sekund att växla mellan dussintals öppna dokument!
  • Minska hundratals musklick åt dig varje dag, säg adjö till mushanden.
  • Ökar din produktivitet med 50% när du tittar på och redigerar flera dokument.
  • Ger effektiva flikar till Office (inkluderar Excel), precis som Chrome, Firefox och ny Internet Explorer.
Skärmdump av Excel (med Office-fliken installerad)
Sortera kommentarer efter
Kommentarer (0)
Inga betyg än. Bli först med att betygsätta!
Det finns inga kommentarer här ännu
Lämna dina kommentarer
Postar som gäst
×
Betygsätt detta inlägg:
0   Tecken
Föreslagna platser