Hoppa till huvudinnehåll

Räkna rader om de uppfyller flera kriterier i Excel

Räkna antalet rader i ett intervall baserat på flera kriterier, varav några beror på de logiska testerna som fungerar på radnivå, SUMPRODUCT-funktionen i Excel kan göra dig en tjänst.

Till exempel har jag en produktrapport med planerad och faktisk försäljning, nu vill jag räkna raderna som innehåller Apple där den faktiska försäljningen är större än den planerade försäljningen enligt nedanstående skärmbild. För att lösa denna uppgift är den mest effektiva funktionen SUMPRODUCT-funktionen.

Räkna rader om de uppfyller flera kriterier med SUMPRODUKT-funktionen


Räkna rader om de uppfyller flera kriterier med SUMPRODUKT-funktionen

För att räkna raderna om de uppfyller flera kriterier, med funktionen SUMPRODUKT i Excel, är den allmänna syntaxen:

=SUMPRODUCT((logical1)*(logical2))
  • logical1, logical2:De logiska uttryck som används för att jämföra värdena.

1. För att räkna antalet Apple-rader där den faktiska försäljningen är större än den planerade försäljningen, använd följande formel:

=SUMPRODUCT(($C$2:$C$10>$B$2:$B$10)*($A$2:$A$10=E2))

Anmärkningar: I ovanstående formel, C2:C10>B2:B10 är det första logiska uttrycket som jämför värdena i kolumn C med värdena i kolumn B; A2: A10 = E2 är det andra logiska uttrycket som kontrollerar om cellen E2 finns i kolumn A.

2. Tryck sedan på ange för att få det resultat du behöver, se skärmdump:


Förklaring av formeln:

=SUMPRODUCT(($C$2:$C$10>$B$2:$B$10)*($A$2:$A$10=E2))

  • $C$2:$C$10>$B$2:$B$10: Detta logiska uttryck används för att jämföra värdena i kolumn C med värdena i kolumn B i varje rad, om värdet i kolumn C är större än värdet i kolumn B visas TRUE, annars visas ett FALSE och returnerar matrisvärdena som detta: {TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE}.
  • $A$2:$A$10=E2:Detta logiska uttryck används för att kontrollera om cellen E2 finns i intervallet A2:A10. Så du kommer att få resultatet så här: {TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE}.
  • ($C$2:$C$10>$B$2:$B$10)*($A$2:$A$10=E2): Multiplikationsoperationen används för att multiplicera dessa två arrayer till en enda array för att returnera resultatet så här: {1;0;1;0;0;0;0;1;0}.
  • SUMPRODUCT(($C$2:$C$10>$B$2:$B$10)*($A$2:$A$10=E2))= SUMPRODUCT({1;0;1;0;0;0;0;1;0}): Denna SUMPRODUKT lägger ihop siffrorna i arrayen och returnerar resultatet: 3.

Relativ funktion som används:

  • SUMPRODUCT:
  • SUMPRODUCT-funktionen kan användas för att multiplicera två eller flera kolumner eller matriser tillsammans och sedan få summan av produkter.

Fler artiklar:

  • Räkna rader om uppfyller interna kriterier
  • Om du antar att du har en rapport om produktförsäljningen för detta år och förra året, och nu kan du behöva räkna produkter där försäljningen i år är större än förra året, eller om försäljningen i år är mindre än förra året enligt nedan skärmdump visas. Normalt kan du lägga till en hjälparkolumn för att beräkna försäljningsskillnaden mellan de två åren och sedan använda RÄTTA för att få resultat. Men i den här artikeln kommer jag att introducera SUMPRODUCT -funktionen för att få resultatet direkt utan någon hjälparkolumn.
  • Räkna matchningar mellan två kolumner
  • Till exempel har jag två listor med data i kolumn A och kolumn C, nu vill jag jämföra de två kolumnerna och räkna om värdet i kolumn A finns i kolumn C på samma rad som nedanstående skärmdump visas. I det här fallet kan SUMPRODUCT -funktionen vara den bästa funktionen för dig att lösa denna uppgift i Excel.
  • Räkna antalet celler är lika med ett av många värden
  • Om jag antar att jag har en lista över produkter i kolumn A, nu vill jag få det totala antalet specifika produkter Apple, Grape and Lemon som anges i intervall C4: C6 från kolumn A enligt nedanstående skärmdump. Normalt fungerar inte Excel i de här funktionerna i enkla COUNTIF- och COUNTIFS -funktioner. Denna artikel, jag kommer att prata om hur man löser detta jobb snabbt och enkelt med kombinationen av SUMPRODUCT och COUNTIF -funktioner.

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 (2)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
=SUMPRODUCT({Array of True/False}) doesn't count the True values in the array anymore (as of the SUM or COUNT formulaes).
But you can force the convertion of True/False to 1 and 0 by adding the '--' operator right before the array:
=SUMPRODUCT(--{Array of True/False}).
You can also type this operator right after the multiplication sign, giving the strange '*--' operator.

In this exemple, a working formulae would be:
=SUMPRODUCT(--($C$2:$C$10>$B$2:$B$10)*--($A$2:$A$10=E2))
This comment was minimized by the moderator on the site
Hello Professor X,

You are right in one way. The double negative (--) is one of several ways to coerce TRUE and FALSE values into their numeric equivalents, 1 and 0. Once we have 1s and 0s, we can perform various operations on the arrays with Boolean logic.

But our formula doesn't need the the double negative (--), making the formula more compact. This is because the math operation of multiplication (*) automatically converts the TRUE and FALSE values to 1s and 0s. Have a nice day.

Sincerely,
Mandy
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations