Som jeg har nevnt i mange av bloggene mine at et SUMPRODUCT er en veldig allsidig funksjon og kan brukes til flere formål. I denne artikkelen vil vi se hvordan vi kan bruke denne funksjonen til å telle verdier med flere ELLER -kriterier.
Generisk SUMPRODUCT Formula å telle med flere eller kriterier
| = SUMPRODUCT (-((((kriterier 1)+(kriterier 2)+… )>0) |
Kriterier 1: Dette er alle kriterier som returnerer en rekke SANN og FALSK.
Kriterier 2: Dette er det neste kriteriet du vil kontrollere. På samme måte kan du ha så mange kriterier du vil.
Den generiske formelen ovenfor er ofte modifisert for å passe kravene for å telle med flere ELLER -kriterier. Men grunnformelen er denne. Først skal vi se hvordan dette fungerer gjennom et eksempel, og etter det vil vi diskutere andre scenarier der du må endre denne formelen litt.
Eksempel: Tell brukere hvis forhandlerkode eller årFyrstikker Bruker SUMPRODUCT
Så her har vi et datasett med selgere. Dataene inneholder mange kolonner. Det vi må gjøre er å telle antall brukere som har koden "INKA" eller året er "2016". Sørg for at hvis noen har begge (koden som "inka" og år 2016), bør den regnes som 1.
Så her har vi to kriterier. Vi bruker ovennevnte SUMPRODUCT -formel:
| = SUMPRODUCT (-(((Kode = I3)+(År = K3))> 0)) |
Her er kode og år navngitt områder.
Dette gir 7.
I dataene har vi 5 poster med INKA -kode og 4 poster fra 2016. Men 2 poster har henholdsvis "INKA" og 2016 som kode og år. Disse postene regnes som 1. Og slik får vi 7.
Hvordan virker det?
Så la oss se på hvordan formelen løses trinnvis, så vil jeg diskutere hvordan den fungerer.
| =SUMPRODUKT(-((((Kode = I3)+(År = K3))> 0)) |
| 1=>SUMPRODUKT(-(({TRUE; FALSE; TRUE; TRUE; TRUE;…}+{FALSE; FALSE; FALSE; TRUE;…})> 0)) |
| 2=>SUMPRODUKT(--(({1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0)) |
| 3=>SUMPRODUKT(-({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE;…}) |
| 4=>SUMPRODUKT({1;0;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0}) |
| 5=>7 |
I det første trinnet blir verdien av I3 ("INKA") sammenlignet med hver celle i kodeområdet. Dette returnerer en rekke SANN og FALSK. SANN for hver kamp. For å spare plass har jeg ikke vist alle TRUE-FALSE. På samme måte samsvarer verdien av K3 (2016) med hver celle i årsområdet.
I neste trinn legger vi til disse to matrisene som resulterer i en ny rekke numeriske verdier. Som du kanskje vet, behandles SANN som 1 og FALSK som 0 i Excel. Så når SANN og SANN legges til får vi 2 og resten kan du forstå.
I det neste trinnet sjekker vi hvilken verdi som er større enn 0 i matrisen. Dette konverterer igjen matrisen til en sann falsk matrise. For hver 0 -verdi vi får, blir False og rest konvertert som sant. Nå er antallet SANN -verdier i matrisen vårt svar. Men hvordan teller vi dem? Dette er hvordan.
Dobbel negative (-) tegn brukes til å konvertere boolske verdier til 1s og 0s. Så hver SANN verdi i matrisen blir konvertert til 1 og FALSK til 0.
I det siste trinnet oppsummerer SUMPRODUCT denne matrisen, og vi får svaret vårt som 7.
Legge til flere eller kriterier for å telle ved hjelp av SUMPRODUCT
Så hvis du trenger å legge til flere eller kriterier for å telle, kan du bare legge til kriterier ved å bruke + -tegnet til funksjonen.
For eksempel, hvis du vil legge til et annet kriterium i formelen ovenfor, slik at det legger til antall ansatte som har solgt mer enn 5 produkter. SUMPRODUCT -formelen vil ganske enkelt se slik ut:
| = SUMPRODUCT (-(((Kode = I3)+(År = K3)+(Salg> 5))> 0)) |
Enkel! ikke sant?
Men la oss si at du vil ha to kriterier fra Kode område. La oss si at du vil telle "INKB". Så hvordan gjør du dette? En metode er å bruke teknikken ovenfor, men det vil være repeterende. La oss si at jeg vil legge til 10 flere kriterier fra samme område. I slike tilfeller er denne teknikken ikke så smart å telle med SUMPRODUCT.
La oss si at vi har ordnet data slik.
Kriteriene er i en rad I2: J2. Ordningen av data er viktig her. SUMPRODUCT -formelen for 3 OR -kriterieteller vil være:
| = SUMPRODUCT (-((((Kode = I2: J2)+(År = I3: J3))> 0)) |
Dette er SUMPRODUCT -formelen for å telle med flere kriterier når flere kriterier fra ett område skrives på rad.
Dette gir det riktige svaret som er 10.
Hvis du skriver et år i J3, vil formelen også legge til det tallet.
Dette brukes når kriteriene er på en rad. Vil det fungere når kriteriene er i en kolonne for samme område? Nei. Det vil ikke.
I dette eksemplet har vi flere koder å telle, men disse typekodene er skrevet i en kolonne. Når vi bruker ovenstående SUMPRODUCT -formel, får vi en #N/A -feil. Vi vil ikke komme inn på hvordan denne feilen oppsto, da dette vil gjøre denne artikkelen for lang. La oss se hvordan vi kan få dette til å fungere.
For å få denne formelen til å fungere, må du pakke inn kodekriteriene i TRANSPOSE -funksjonen. Dette vil få formelen til å fungere.
| = SUMPRODUCT (-((((Code = TRANSPOSE (H3: H4))+(Year = TRANSPOSE (I3: I4)))> 0)) |
Dette er formelen for å telle med flere eller betingelser i samme område når kriterier er oppført i en kolonne.
Så ja kompis, jeg håper jeg var tydelig nok, og det var fornuftig. Jeg håper det tjener din hensikt å være her. Hvis denne formelen ikke løste problemet ditt, gi meg beskjed om dine krav i kommentarfeltet nedenfor. Jeg vil mer enn gjerne hjelpe deg på noen måte. Du kan nevne enhver tvilrelatert Excel/VBA. Frem til da fortsett å lære, fortsett å utmerke deg.
Slik bruker du SUMPRODUCT -funksjonen i Excel: Returnerer SUMMEN etter multiplikasjon av verdier i flere matriser i excel. Denne funksjonen kan brukes til å utføre flere oppgaver. Dette er en av de mest allsidige funksjonene.
TELLER med Dynamic Criteria Range : For å telle med dynamiske kriterier, bruker vi ganske enkelt INDIRECT -funksjonen. Denne funksjonen kan
TELLER MED ELLER For flere kriterier : Tell celler som har flere kriterier som samsvarer med OR -funksjonen. For å sette en ELLER -logikk i COUNTIFS -funksjonen trenger du ikke å bruke OR -funksjonen.
Bruke IF med AND / OR Functions i Microsoft Excel : Disse logiske funksjonene brukes til å utføre beregninger av flere kriterier. Med IF brukes OR og AND -funksjonene til å inkludere eller ekskludere treff.
Slik bruker du OR -funksjonen i Microsoft Excel : Funksjonen brukes til å inkludere alle de SANNE verdiene i flere kriterier.
Hvordan telle celler som inneholder dette eller det i Excel i Excel : Til celler som inneholder dette eller det, kan vi bruke SUMPRODUCT -funksjonen. Slik gjør du disse beregningene.
Populære artikler:
50 Excel -snarveier for å øke produktiviteten | Bli raskere på oppgaven din. Disse 50 snarveiene får deg til å jobbe enda raskere med Excel.
Slik bruker du Excel VLOOKUP -funksjon| Dette er en av de mest brukte og populære funksjonene til excel som brukes til å slå opp verdi fra forskjellige områder og ark.
Slik bruker du Excel COUNTIF -funksjon| Tell verdier med betingelser ved hjelp av denne fantastiske funksjonen. Du trenger ikke å filtrere dataene dine for å telle spesifikke verdier. Countif -funksjonen er avgjørende for å forberede dashbordet.
Slik bruker du SUMIF -funksjonen i Excel | Dette er en annen viktig funksjon på dashbordet. Dette hjelper deg med å oppsummere verdier på spesifikke forhold.
