Som jag har nämnt i många av mina bloggar att en SUMPRODUCT är en mycket mångsidig funktion och kan användas för flera ändamål. I den här artikeln kommer vi att se hur vi kan använda den här funktionen för att räkna värden med flera ELLER -kriterier.
Generisk SUMPRODUCT -formel att räkna med flera eller kriterier
| = SUMPRODUCT (-((((kriterier1)+(kriterier2)+… )>0) |
Kriterier 1: Detta är alla kriterier som returnerar en matris med SANT och FALSKT.
Kriterier2: Detta är nästa kriterium som du vill kontrollera. På samma sätt kan du ha så många kriterier du vill.
Ovanstående generiska formel modifieras ofta för att passa kraven att räkna med flera ELLER -kriterier. Men grundformeln är detta. Först ska vi se hur detta fungerar genom ett exempel och därefter kommer vi att diskutera andra scenarier där du måste ändra denna formel lite.
Exempel: Räkna användare om dealerkod eller årTändstickor Använda SUMPRODUCT
Så här har vi en uppsättning säljare. Data innehåller många kolumner. Vad vi behöver göra är att räkna antalet användare som har koden "INKA" eller år är "2016". Se till att om någon har både (kod som "inka" och år 2016) ska det räknas som 1.
Så här har vi två kriterier. Vi använder ovanstående SUMPRODUCT -formel:
| = SUMPRODUCT (-(((Kod = I3)+(År = K3))> 0)) |
Här kallas kod och årsområden.
Detta returnerar 7.
I data har vi 5 register över INKA -kod och 4 poster för år 2016. Men 2 poster har både "INKA" och 2016 som kod respektive år. Dessa poster räknas som 1. Och så här får vi 7.
Hur fungerar det?
Så låt oss ta en titt på hur formeln löses steg för steg, sedan kommer jag att diskutera hur det fungerar.
| =SUMPRODUKT(-(((Kod = 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örsta steget jämförs värdet på I3 ("INKA") med varje cell i kodområdet. Detta returnerar en matris med SANT och FALSKT. SANT för varje match. För att spara utrymme har jag inte visat allt SANT-FALSKT. På samma sätt matchas värdet på K3 (2016) med varje cell i årsintervallet.
I nästa steg lägger vi till dessa två matriser som resulterar i en ny uppsättning numeriska värden. Som du kanske vet behandlas SANT som 1 och FALSKT som 0 i Excel. Så när SANT och SANT läggs till får vi 2 och resten kan du förstå.
I nästa steg kontrollerar vi vilket värde som är större än 0 i matrisen. Detta omvandlar igen matrisen till en sann falsk matris. För varje 0 -värde vi får omvandlas False och rest som sant. Nu är antalet SAN -värden i matrisen vårt svar. Men hur räknar vi dem? Här är hur.
Dubbla negativa (-) tecken används för att omvandla booleska värden till 1: or och 0: or. Så varje SANT värde i matrisen omvandlas till 1 och FALSKT till 0.
I det sista steget summerar SUMPRODUCT denna array och vi får vårt svar som 7.
Lägga till fler eller kriterier att räkna med SUMPRODUCT
Så om du behöver lägga till fler eller kriterier att räkna kan du bara lägga till kriterier med hjälp av + -tecknet till funktionen.
Till exempel, om du vill lägga till ytterligare kriterier i ovanstående formel så att det lägger till antalet anställda som har sålt mer än 5 produkter. SUMPRODUCT -formeln kommer helt enkelt att se ut så här:
| = SUMPRODUCT (-(((Kod = I3)+(År = K3)+(Försäljning> 5))> 0)) |
Enkel! är det inte?
Men låt oss säga att du vill ha två kriterier från Koda räckvidd. Låt oss säga att du vill räkna "INKB". Så hur gör du det här? En metod är att använda ovanstående teknik men det skulle vara repetitivt. Låt oss säga att jag vill lägga till ytterligare 10 kriterier från samma intervall. I sådana fall är denna teknik inte så smart att räkna med SUMPRODUCT.
Låt oss säga att vi har data ordnade så här.
Kriteriekoderna finns i en rad I2: J2. Upplägget av data är viktigt här. SUMPRODUCT -formeln för 3 ELLER kriteriernas inställningar kommer att vara:
| = SUMPRODUCT (-(((Kod = I2: J2)+(År = I3: J3))> 0)) |
Detta är SUMPRODUCT -formeln för att räkna med flera kriterier när flera kriterier från ett intervall skrivs i rad.
Detta returnerar rätt svar som är 10.
Om du skriver något år i J3, kommer formeln att lägga till det antalet också.
Detta används när kriterierna finns i en rad. Fungerar det när kriterierna finns i en kolumn för samma intervall? Nej. Det gör det inte.
I det här exemplet har vi flera koder att räkna men dessa typkoder skrivs i en kolumn. När vi använder ovanstående SUMPRODUCT -formel får vi ans #N/A -fel. Vi kommer inte att gå in på hur detta fel uppstod eftersom detta kommer att göra denna artikel för lång. Låt oss se hur vi kan få det här att fungera.
För att få denna formel att fungera måste du slå in kodkriterierna i TRANSPOSE -funktionen. Detta kommer att få formeln att fungera.
| = SUMPRODUCT (-((((Kod = TRANSPOSE (H3: H4))+(År = TRANSPOSE (I3: I4)))> 0)) |
Detta är formeln för att räkna med flera eller villkor i samma intervall när kriterier anges i en kolumn.
Så ja kompis, jag hoppas att jag var tillräckligt tydlig och det var vettigt. Jag hoppas att det tjänar ditt syfte att vara här. Om denna formel inte löste ditt problem, låt mig veta dina krav i kommentarfältet nedan. Jag hjälper dig mer än gärna på något sätt. Du kan nämna alla tvivelrelaterade Excel/VBA. Tills dess fortsätt att lära dig, fortsätt att utmärka dig.
Så här använder du SUMPRODUCT -funktionen i Excel: Returnerar SUMMEN efter multiplikation av värden i flera matriser i excel. Denna funktion kan användas för att utföra flera uppgifter. Detta är en av de mest mångsidiga funktionerna.
RÄKNINGAR med Dynamic Criteria Range : För att räkna med dynamiska kriterier använder vi helt enkelt INDIRECT -funktionen. Denna funktion kan
RÄKNINGAR MED ELLER För flera kriterier : Räkna celler som har flera kriterier som matchar med hjälp av ELLER -funktionen. För att sätta en ELLER -logik i COUNTIFS -funktionen behöver du inte använda OR -funktionen.
Använda IF med AND / OR -funktioner i Microsoft Excel : Dessa logiska funktioner används för att utföra beräkningar av flera kriterier. Med IF används OR- och AND -funktionerna för att inkludera eller utesluta matchningar.
Hur man använder ELLER -funktionen i Microsoft Excel : Funktionen används för att inkludera alla SAN -värden i flera kriterier.
Hur man räknar celler som innehåller detta eller det i Excel i Excel : Till celler som innehåller det eller det kan vi använda SUMPRODUCT -funktionen. Så här gör du dessa beräkningar.
Populära artiklar:
50 Excel -genvägar för att öka din produktivitet | Bli snabbare på din uppgift. Dessa 50 genvägar får dig att arbeta ännu snabbare med Excel.
Hur man använder Excel VLOOKUP -funktion| Detta är en av de mest använda och populära Excel -funktionerna som används för att leta upp värde från olika intervall och ark.
Hur man använder Excel COUNTIF -funktion| Räkna värden med förhållanden med denna fantastiska funktion. Du behöver inte filtrera dina data för att räkna specifika värden. Countif -funktionen är avgörande för att förbereda din instrumentpanel.
Hur man använder SUMIF -funktionen i Excel | Detta är en annan viktig instrumentpanel. Detta hjälper dig att summera värden på specifika förhållanden.