Zoals ik in veel van mijn blogs heb vermeld, is een SOMPRODUCT een zeer veelzijdige functie en voor meerdere doeleinden te gebruiken. In dit artikel zullen we zien hoe we deze functie kunnen gebruiken om waarden met meerdere OF-criteria te tellen.
Generieke SOMPRODUCT-formule om te tellen met meerdere of criteria
| =SOMPRODUCT(--((((criteria1)+(criteria2)+… )>0) |
Criteria1: Dit is elk criterium dat een array van TRUE en FALSE retourneert.
Criteria2: Dit zijn de volgende criteria die u wilt controleren. Op dezelfde manier kunt u zoveel criteria hebben als u wilt.
De bovenstaande generieke formule wordt vaak aangepast om te voldoen aan de vereisten om te tellen met meerdere OR-criteria. Maar de basisformule is deze. Eerst zullen we aan de hand van een voorbeeld zien hoe dit werkt en daarna zullen we andere scenario's bespreken waarin u deze formule een beetje moet aanpassen.
Voorbeeld: tel gebruikers als dealercode of jaarWedstrijden SOMPRODUCT gebruiken
Dus hier hebben we een dataset van verkopers. De gegevens bevatten veel kolommen. Wat we moeten doen, is het aantal gebruikers tellen met de code "INKA" of het jaar is "2016". Zorg ervoor dat als iemand beide heeft (code als "inka" en jaartal 2016) dit als 1 moet worden geteld.
Hier hebben we dus twee criteria. We gebruiken de bovengenoemde SOMPRODUCT-formule:
| =SOMPRODUCT(--(((Code=I3)+(Jaar=K3))>0)) |
Hier worden code en jaar benoemde bereiken.
Dit geeft 7 terug.
In de gegevens hebben we 5 records van INKA-code en 4 records van het jaar 2016. Maar 2 records hebben zowel "INKA" als 2016 als respectievelijk code en jaar. Deze records worden geteld als 1. En zo krijgen we 7.
Hoe werkt het?
Dus laten we eens kijken hoe de formule stap voor stap wordt opgelost, dan zal ik bespreken hoe het werkt.
| =SOMPRODUCT(--(((Code=I3)+(Jaar=K3))>0)) |
| 1=>SOMPRODUCT(--(({TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;… }+{FALSE;FALSE;FALSE;TRUE;TRUE;… })>0)) |
| 2=>SOMPRODUCT(--(({1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0)) |
| 3=>SOMPRODUCT(--({TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;… }) |
| 4=>SOMPRODUCT({1;0;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0}) |
| 5=>7 |
In de eerste stap wordt de waarde van I3 ("INKA") vergeleken met elke cel in het codebereik. Dit retourneert een array van TRUE en FALSE. WAAR voor elke wedstrijd. Om ruimte te besparen heb ik niet alle TRUE-FALSE getoond. Evenzo wordt de waarde van K3 (2016) vergeleken met elke cel in het jaarbereik.
In de volgende stap voegen we deze twee arrays toe, wat resulteert in een nieuwe array met numerieke waarden. Zoals u wellicht weet, wordt WAAR behandeld als 1 en ONWAAR als 0 in Excel. Dus als TRUE en TRUE worden toegevoegd, krijgen we 2 en de rest kun je begrijpen.
In de volgende stap controleren we welke waarde groter is dan 0 in de array. Dit zet de array opnieuw om in een echte false array. Voor elke 0-waarde die we krijgen, worden False en rest geconverteerd als waar. Nu is het aantal TRUE-waarden in de array ons antwoord. Maar hoe tellen we ze? Hier is hoe.
Dubbele negatieve (--) tekens worden gebruikt om booleaanse waarden om te zetten in enen en nullen. Dus elke TRUE-waarde in de array wordt geconverteerd naar 1 en FALSE naar 0.
In de laatste stap somt het SOMPRODUCT deze array op en krijgen we ons antwoord als 7.
Meer of criteria toevoegen om te tellen met SUMPRODUCT
Dus als u meer of criteria moet toevoegen om te tellen, kunt u gewoon criteria toevoegen met het + teken aan de functie.
Als u bijvoorbeeld nog een criterium aan de bovenstaande formule wilt toevoegen, zodat deze het aantal werknemers optelt dat meer dan 5 producten heeft verkocht. De SOMPRODUCT-formule ziet er eenvoudig als volgt uit:
| =SOMPRODUCT(--(((Code=I3)+(Jaar=K3)+(Verkoop>5))>0)) |
Eenvoudig! is het niet?
Maar laten we zeggen dat u twee criteria wilt hebben van Code bereik. Stel dat u "INKB" wilt tellen. Dus hoe doe je dit? Een methode is het gebruik van de bovenstaande techniek, maar dat zou repetitief zijn. Laten we zeggen dat ik nog 10 criteria uit hetzelfde bereik wil toevoegen. In zulke gevallen is deze techniek niet zo slim om te tellen met SOMPRODUCT.
Laten we zeggen dat we de gegevens zo hebben gerangschikt.
De criteriacodes staan in één rij I2:J2. De rangschikking van gegevens is hierbij van belang. De SOMPRODUCT-formule voor instellingen voor het tellen van 3 OF-criteria is:
| =SOMPRODUCT(--(((Code=I2:J2)+(Jaar=I3:J3))>0)) |
Dit is de SOMPRODUCT-formule om met meerdere criteria te tellen wanneer meerdere criteria uit één bereik achter elkaar worden geschreven.
Dit geeft het juiste antwoord, namelijk 10.
Als u een jaar in J3 typt, voegt de formule dat aantal ook toe.
Dit wordt gebruikt wanneer de criteria op één rij staan. Werkt het als de criteria voor hetzelfde bereik in één kolom staan? Nee. Dat zal niet.
In dit voorbeeld hebben we meerdere codes om te tellen, maar deze typecodes zijn in één kolom geschreven. Wanneer we de bovenstaande SOMPRODUCT-formule gebruiken, krijgen we een ans #N/A-fout. We zullen niet ingaan op hoe deze fout tot stand is gekomen, omdat dit dit artikel te lang zal maken. Laten we eens kijken hoe we dit kunnen laten werken.
Om deze formule te laten werken, moet u de codecriteria in de TRANSPOSE-functie inpakken. Dit zorgt ervoor dat de formule werkt.
| =SOMPRODUCT(--(((Code=TRANSPOSE(H3:H4))+(Jaar=TRANSPOSE(I3:I4)))>0)) |
Dit is de formule voor het tellen met meerdere of voorwaarden in hetzelfde bereik wanneer criteria in een kolom worden vermeld.
Dus ja maat, ik hoop dat ik duidelijk genoeg was en dat het logisch was. Ik hoop dat het je doel dient om hier te zijn. Als deze formule uw probleem niet heeft opgelost, laat me dan uw vereisten weten in de opmerkingen hieronder. Ik zal meer dan blij zijn om u te helpen op welke manier dan ook. U kunt twijfel gerelateerde Excel/VBA vermelden. Tot die tijd blijven leren, blijven excelleren.
Hoe de SOMPRODUCT-functie in Excel te gebruiken?: Retourneert de SUM na vermenigvuldiging van waarden in meerdere arrays in Excel. Deze functie kan worden gebruikt om meerdere taken uit te voeren. Dit is een van de meest veelzijdige functies.
AANTALLEN.ALS met dynamisch criteriumbereik : Om met dynamisch criteriabereik te tellen, gebruiken we gewoon de INDIRECT-functie. Deze functie kan
AANTALLEN MET OF Voor meerdere criteria : tel cellen met meerdere criteria die overeenkomen met behulp van de OF-functie. Om een OF-logica in de AANTAL.ALS-functie te plaatsen, hoeft u de OF-functie niet te gebruiken.
De ALS met EN/OF-functies gebruiken in Microsoft Excel : Deze logische functies worden gebruikt om berekeningen met meerdere criteria uit te voeren. Bij IF worden de OF- en AND-functies gebruikt om overeenkomsten op te nemen of uit te sluiten.
De OF-functie gebruiken in Microsoft Excel : De functie wordt gebruikt om alle TRUE-waarden in meerdere criteria op te nemen.
Cellen tellen die dit of dat bevatten in Excel in Excel :Voor cellen die dit of dat bevatten, kunnen we de SOMPRODUCT-functie gebruiken. Hier is hoe je die berekeningen doet.
Populaire artikels:
50 Excel-snelkoppelingen om uw productiviteit te verhogen | Word sneller in uw taak. Met deze 50 sneltoetsen werk je nog sneller in Excel.
Hoe de Excel VERT.ZOEKEN-functie te gebruiken| Dit is een van de meest gebruikte en populaire functies van Excel die wordt gebruikt om waarde op te zoeken uit verschillende bereiken en bladen.
Hoe de Excel te gebruiken? AANTAL.ALS-functie| Tel waarden met voorwaarden met behulp van deze geweldige functie. U hoeft uw gegevens niet te filteren om specifieke waarden te tellen. Countif-functie is essentieel om uw dashboard voor te bereiden.
Hoe de SUMIF-functie in Excel te gebruiken? | Dit is een andere essentiële functie van het dashboard. Dit helpt u bij het optellen van waarden voor specifieke voorwaarden.