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.
Probleem:
De prijs in een cel moet nauwkeurig worden verdeeld in 5 vergelijkbare betalingen. Wanneer we simpelweg de prijs van $ 88,04 delen door 5 en het resultaat afronden op twee decimalen, krijgen we individuele betalingen van $ 17,61. Dit zou echter neerkomen op een totale betaling van $ 88,05, wat niet het exacte bedrag is waarmee we beginnen. Daarom willen we 5 vergelijkbare getallen berekenen die optellen tot het totale bedrag.
Oplossing:
Eerste Bereken de eerste betaling door de prijs te delen door 5 en het resultaat af te ronden.
Gebruik de functie RONDE in de volgende formule:
=ROND(B1/5,2)
Stap 2:
Bereken elke resterende betaling.
Gebruik de functies ROUND, SUM en ROW in de volgende formule:
=RONDE(($B$1-SOM($B$2:B2))/(5-RIJ()+RIJ($B$2)),2)
Voorbeeld:
Tel gebruikers als de dealercode overeenkomt of het jaar met behulp van SUMPRODUCT
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) als 1 moet worden geteld.
Dus, hoor, we hebben 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. En deze records worden geteld als 1. En zo krijgen we 7.
Laten we eens kijken hoe het werkt?
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))
- SOMPRODUCT(--(({TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;… }+{FALSE;FALSE;FALSE;TRUE;TRUE;… })>0))
- SOMPRODUCT(--(({1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0))
- SOMPRODUCT(--({TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;… })
- SOMPRODUCT({1;0;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})
7
De 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.
Meer of criteria toevoegen om te tellen met behulp van SOMPRODUCT, 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 uit het codebereik wilt hebben. Stel dat u "INKB" wilt tellen. Hoe doe je dit dan? 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 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 in een rij zijn geschreven.
De bovenstaande formule retourneert 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. Zal het werken als de criteria in één kolom voor hetzelfde bereik zijn? 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 komt, omdat dit dit artikel te lang maakt. 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.
Hier zijn alle observatienotities met betrekking tot het gebruik van de formule.
Opmerkingen:
- De formule werkt zowel voor tekst als voor getallen.
- De opzoekwaarden kunnen worden opgegeven als celverwijzing of rechtstreeks met het aanhalingsteken ( " ) in de formule als argumenten.
Ik hoop dat je hebt begrepen hoe je een bedrag in gelijke betalingen kunt verdelen terwijl je verdelings-/afrondingsfouten in Excel vermijdt. Ontdek hier meer artikelen over Excel-opzoekwaarde en Excel 2019-functies. Als je onze blogs leuk vond, deel deze dan met je vrienden op Facebook. En je kunt ons ook volgen op Twitter en Facebook. We horen graag van je, laat ons weten hoe we ons werk kunnen verbeteren, aanvullen of vernieuwen en het voor jou beter kunnen maken. Schrijf ons op de e-mailsite.
Gebruik INDEX en MATCH om waarde op te zoeken : INDEX & MATCH-functie om desgewenst de waarde op te zoeken.
SUM-bereik met INDEX in Excel : Gebruik de INDEX-functie om de SUM van de waarden te vinden zoals vereist.
Hoe de INDEX-functie in Excel te gebruiken? : Vind de INDEX van een array met behulp van de INDEX-functie uitgelegd met een voorbeeld.
Hoe de MATCH-functie in Excel te gebruiken? : Zoek de MATCH in de array met behulp van de INDEX-waarde in de MATCH-functie uitgelegd met een voorbeeld.
Hoe de LOOKUP-functie in Excel te gebruiken? : Zoek de opzoekwaarde in de array met behulp van de LOOKUP-functie uitgelegd met een voorbeeld.
Populaire artikels :
Hoe de IF-functie in Excel te gebruiken? : De IF-instructie in Excel controleert de voorwaarde en retourneert een specifieke waarde als de voorwaarde WAAR is of retourneert een andere specifieke waarde als ONWAAR.
Hoe de VERT.ZOEKEN-functie in Excel 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 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.
Hoe de AANTAL.ALS-functie in Excel te gebruiken? : 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.