I denne artikkelen lærer vi å SUMMERE HVIS ved å bruke oppslagsverdier i Excel.
Med enkle ord, mens vi arbeider med forskjellige datatabeller, må vi noen ganger beregne verdier i de første tabellbaserte verdiene i en annen tabell. Betingelse er å få summen av verdier i den første tabellen ved hjelp av de andre tabellverdiene
For denne artikkelen trenger vi følgende funksjoner:
- SUMPRODUCT -funksjon
- SUMIF -funksjon
SUMPRODUCT -funksjonen er en matematisk funksjon i Excel. Den opererer på flere områder. Den multipliserer de tilsvarende matrisene og legger dem deretter til.
Totalt = (A1 * B1 * C1 *… + A2 * B2 * C2 *… +…)
Syntaks:
= SUMPRODUCT (array1, [array2], …)
array: Det er området eller en liste over verdier.
SUMIF -funksjonen i Excel returnerer SUMMEN av tallene som har oppfylt de angitte kriteriene. SUM -område og kriterier kan være like eller forskjellige.
Generisk Excel SUMIF -formel:
= SUMIF (kriterieområde, kriterier, sumområde)
criteria_range: område der betingelsen gjelder.
kriterier: betingelse for å søke på criteria_range.
sum_range: sum -område på grunnlag av kriterier.
Nå skal vi lage en formel av disse funksjonene. Her vil vi gi dataene, og vi trengte sumresultater der verdien samsvarer med verdien i oppslagstabellen.
Generisk formel:
= SUMPRODUCT (SUMIF (resultat, poster, sum_nums))
resultat : verdier som skal matches i oppslagstabellen
ta opp : verdier i den første tabellen
sum_nums : verdier som skal summeres i henhold til matchende verdier.
La oss teste denne formelen ved å kjøre den på et eksempel
Her beregner vi de totale poengene til et fotballspillende lag ved hjelp av poengtabellen eller oppslagstabellen.
Her er resultatet (B8: B10) gitt som navngitt område og poeng (C8: C10) gitt som navngitt område.
Nå vil vi bruke formelen nedenfor for å få SUM av poeng for måned1 i F3 -celle
Bruk formelen:
= SUMPRODUCT (SUMIF (resultat, B3: E3, poeng))
Forklaring:
- SUMIF -funksjonen matcher resultatet med verdier i B3: E3.
- Deretter returneres poeng som tilsvarer resultatet til SUMPRODUCT -funksjonen.
- SUMPRODUCT -funksjonen returnerer SUMMEN av verdier som er gitt som array til funksjonen.
Som du kan se her er det totale poenget laget laget i første måned er 7.
3 (W) + 0 (L) + 1 (D) + 3 (W) = 7 poeng
Kopier formelen i andre celler, velg cellene som tar den første cellen der formelen allerede er brukt, bruk hurtigtasten Ctrl + D eller bruk dra ned cellealternativet i excel.
Som du kan se i øyeblikksbildet ovenfor, scoret poengene i de fire forskjellige månedene.
Få SUMMEN av alle poeng i 4 måneder.
Det betyr at laget scoret totalt 18 poeng på 4 måneder.
Alternativ metode
Bruk metoden SUM og matrise i stedet for å bruke SUMPRODUCT -funksjonen.
Bruk formelen:
{= SUM (SUMIF (resultat, B3: E3, poeng))}
Krøllete seler er matrisemetoden for å foreslå at funksjonen beregner all returnert verdi etter SUMIF -funksjon.
Merk: Ikke gjør det bruk symbolene for krøllbøyler manuelt. Bruke Ctrl + Shift + Enter å bruke matrisemetoden i Excel.
Som du kan se fra formelen ovenfor, kan du få totalt poeng ved hjelp av betinget oppslagstabell.
Merknader:
- Formelen fungerer bare med tall.
- Formelen fungerer bare når det ikke er noen dubletter i oppslagstabellen
- SUMPRODUCT -funksjonen anser ikke -numeriske verdier som 0s.
- SUMPRODUCT -funksjonen anser logisk verdi TRUE som 1 og Falsk som 0.
- Argumentmatrisen må ha samme lengde, ellers funksjonen
Håper denne artikkelen om hvordan du bare returnerer SUM fra formler i Excel er forklarende. Finn flere artikler om SUMPRODUCT -funksjoner her. Vennligst del spørringen nedenfor i kommentarfeltet. Vi hjelper deg.
Slik bruker du SUMPRODUCT -funksjonen i Excel: Returnerer SUMMEN etter multiplikasjon av verdier i flere matriser i excel.
SUM hvis datoen er mellom : Returnerer SUMMEN av verdier mellom gitte datoer eller periode i excel.
Sum hvis datoen er større enn gitt dato: Returnerer SUMMEN av verdier etter gitt dato eller periode i excel.
2 måter å summere etter måned i Excel: Returnerer SUMMEN av verdier i en gitt spesifikk måned i excel.
Slik summerer du flere kolonner med betingelse: Returnerer SUM av verdier på tvers av flere kolonner som har betingelse i excel
Populære artikler
50 Excel -snarvei for å øke produktiviteten
Rediger en rullegardinliste
Absolutt referanse i Excel
Hvis det er betinget formatering
Hvis det er jokertegn
Vlookup etter dato
Konverter tommer til fot og tommer i Excel 2016
Bli med for- og etternavn i excel
Telle celler som matcher enten A eller B