Hoppa till huvudinnehåll

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

Populära funktioner: Hitta, markera eller identifiera dubbletter  |  Ta bort tomma rader  |  Kombinera kolumner eller celler utan att förlora data  |  Rund utan formel ...
Super VLookup: Flera kriterier  |  Multipelt värde  |  Över flera ark  |  Fuzzy Lookup...
Adv. Rullgardinslista: Enkel rullgardinslista  |  Beroende rullgardinslista  |  Flervals-rullgardinslista...
Kolumnhanterare: Lägg till ett specifikt antal kolumner  |  Flytta kolumner  |  Växla synlighetsstatus för dolda kolumner  Jämför kolumner med Välj Samma och olika celler ...
Utvalda funktioner: Rutnätsfokus  |  Designvy  |  Stor formelbar  |  Arbetsbok & Bladhanterare | Resursbibliotek (Automatisk text)  |  Datumväljare  |  Kombinera arbetsblad  |  Kryptera/Dekryptera celler  |  Skicka e-postmeddelanden efter lista  |  Superfilter  |  Specialfilter (filtrera fet/kursiv/genomstruken...) ...
Topp 15 verktygssatser12 text verktyg (lägga till text, Ta bort tecken ...)  |  50 + Diagram Typer (Gantt Chart ...)  |  40+ Praktiskt Formler (Beräkna ålder baserat på födelsedag ...)  |  19 Införande verktyg (Infoga QR-kod, Infoga bild från sökväg ...)  |  12 Konvertering verktyg (Siffror till ord, Valutaväxling ...)  |  7 Slå ihop och dela verktyg (Avancerade kombinera rader, Dela upp Excel-celler ...)  |  ... och mer

Kutools för Excel har över 300 funktioner, Se till att det du behöver bara är ett klick bort...


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 visar och redigerar flera dokument.
  • Ger effektiva flikar till Office (inklusive Excel), precis som Chrome, Edge och Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations