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 denna funktion för att räkna värden med flera eller kriterier.
Generisk SUMPRODUCT -formel att räkna med flera eller kriterier
| = SUMPRODUCT (-(((kriterium1)+(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.
Problem:
Priset i en cell ska delas upp exakt i 5 liknande betalningar. När vi bara delar priset 88,04 dollar med 5 och avrundar resultatet till två decimaler får vi individuella betalningar på 17,61 dollar. Detta skulle dock utgöra en total betalning på $ 88,05, vilket inte är det exakta beloppet vi börjar med. Därför vill vi beräkna 5 liknande nummer som summerar det totala beloppet.
Lösning:
Först beräkna den första betalningen genom att dela priset med 5 och avrunda resultatet.
Använd funktionen RUND i följande formel:
= RUND (B1/5,2)
Steg 2:
Beräkna varje återstående betalning.
Använd funktionerna RUND, SUMMA och RAD i följande formel:
= RUND (($ B $ 1-SUM ($ B $ 2: B2))/(5-RAD ()+RAD ($ B $ 2)), 2)
Exempel:
Räkna användare om dealerkoden matchar eller år med 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 räknas som 1.
Så hör att vi har 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. Och dessa poster räknas som 1. Och så här får vi 7.
Låt oss se hur det fungerar?
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))
- SUMPRODUKT(-(({TRUE; FALSE; TRUE; TRUE; TRUE;…}+{FALSE; FALSE; FALSE; TRUE;…})> 0))
- SUMPRODUKT(--(({1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0))
- SUMPRODUKT(-({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE;…})
- SUMPRODUKT({1;0;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})
7
De dubbla negativa (-) tecknen 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.
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 i 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 kodintervallet. Låt oss säga att du vill räkna "INKB". Hur gör du då detta? 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ällning 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.
Ovanstående formel returnerar det korrekta svaret 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 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 kommer eftersom det gör den här artikeln 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.
Här är alla observationsanteckningar om användning av formeln.
Anmärkningar:
- Formeln fungerar för både text och siffror.
- Uppslagsvärdena kan ges som cellreferens eller direkt med citatsymbol (") i formeln som argument.
Hoppas du förstod hur man delar upp ett belopp i lika betalningar samtidigt som man undviker uppdelning / avrundning av fel i Excel. Utforska fler artiklar om Excel -sökvärde och Excel 2019 -funktioner här. Om du gillade våra bloggar, dela den med dina vänner på Facebook. Och du kan också följa oss på Twitter och Facebook. Vi vill gärna höra från dig, låt oss veta hur vi kan förbättra, komplettera eller förnya vårt arbete och göra det bättre för dig. Skriv till oss på e -postwebbplatsen.
Använd INDEX och MATCH för att leta upp värde : INDEX & MATCH -funktion för att slå upp värdet efter behov.
SUM -intervall med INDEX i Excel : Använd INDEX -funktionen för att hitta SUMMAN för värdena efter behov.
Så här använder du INDEX -funktionen i Excel : Hitta INDEX för en array med hjälp av INDEX -funktionen som förklaras med ett exempel.
Hur man använder MATCH -funktionen i Excel : Hitta MATCH i matrisen med INDEX -värdet inuti MATCH -funktionen som förklaras med exempel.
Hur man använder LOOKUP -funktionen i Excel : Hitta uppslagsvärdet i arrayen med hjälp av LOOKUP -funktionen som förklaras med ett exempel.
Populära artiklar:
Hur man använder IF -funktionen i Excel : IF -satsen i Excel kontrollerar villkoret och returnerar ett specifikt värde om villkoret är SANT eller returnerar ett annat specifikt värde om det är FALSKT.
Så här använder du VLOOKUP -funktionen i Excel : 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 SUMIF -funktionen i Excel : Detta är en annan viktig instrumentpanel. Detta hjälper dig att summera värden på specifika förhållanden.
Hur man använder funktionen RÄNKNING i Excel : Räkna värden med villkor 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.