Hoe COUNTIFS, SUMIFS, AVERAGEIFS in Excel te gebruiken

De drie meest gebruikte formules in Excel die eenvoudige wiskundige berekeningen uitvoeren, zijn AANTAL , SOM en GEMIDDELDE . Of je nu een financieel budget in Excel beheert of gewoon je volgende vakantie bijhoudt, je hebt waarschijnlijk al een van deze functies gebruikt.

In dit artikel gaan we de basisprincipes van deze drie functies en hun relevante en nuttige tegenhangers doornemen: AANTALLEN.ALS, SOMMEN.ALS en GEMIDDELDEN.ALS.

Laten we zeggen dat we een nieuw online bedrijf starten dat mobiele telefoons verkoopt en dat we een blad hebben met de verkopen die we in de eerste twee maanden hebben gedaan. Download hier een voorbeeld van een Excel-spreadsheet .

Excel COUNT, SUM en GEMIDDELDE

Om te weten hoeveel mobiele telefoons we hebben verkocht, kunnen we snel de COUNT- formule gebruiken, zoals hieronder weergegeven:

=AANTAL(E2:E16)

Aan de andere kant, om het totale aantal verkopen te krijgen dat we hebben gemaakt, kunnen we de SUM- formule gebruiken zoals hieronder weergegeven:

=SOM(E2:E16)

Ten slotte kunnen we de GEMIDDELDE formule gebruiken om de gemiddelde verkopen te achterhalen die we voor alle telefoons hebben gemaakt :

=GEMIDDELDE(E2:E16)

Het resultaat zou moeten zijn zoals hieronder:

COUNT-, SUM- en GEMIDDELDE formules werken alleen voor records waarvan de celwaarde in getalnotatie is. Elk record binnen het formulebereik (bijv. E2:E16 in dit voorbeeld) dat niet de getalnotatie heeft, wordt genegeerd.

Zorg er dus voor dat alle cellen in de formule COUNT, SUM en GEMIDDELDE allemaal zijn opgemaakt als Number , niet als Text . Probeer dezelfde formule te gebruiken, maar met E:E als bereik in plaats van E2:E16 . Het geeft hetzelfde resultaat als voorheen omdat het de koptekst negeert (dwz Verkoopprijs ), die in tekstformaat is.

Wat als we nu het aantal verkopen, het totale aantal verkopen en het gemiddelde aantal verkopen per telefoon willen weten, alleen voor degenen die in de VS zijn verkocht? Dit is waar AANTALLEN, SOMMEN en GEMIDDELDEN.ALS een belangrijke rol spelen. Let op de onderstaande formule:

AANTALLEN

Uitsplitsing formule:

1. =AANTAL.ALS( – De "=" geeft het begin van een formule in de cel aan en AANTALLEN.ALS is het eerste deel van de Excel-functie die we gebruiken.
2. D2:D16 – Verwijst naar het gegevensbereik om te controleren of het voldoet aan de criteria die moeten worden opgenomen in de telformule.
3. "USA" - Criteria waarnaar moet worden gezocht in het opgegeven gegevensbereik ( D2:D16 )
4. ) – Sluithaakje dat het einde van de formule aangeeft.

De formule retourneert 6, het aantal verkopen voor producten die vanuit het magazijn in de VS zijn verzonden.

SOMMEN

Uitsplitsing formule:

1. =SUMIFS( – De “=” geeft weer het begin van de formule aan.
2. E2:E16 – Verwijst naar het gegevensbereik dat we willen optellen, dwz de verkoopprijs in ons voorbeeld.
3. D2:D16 – Verwijst naar het gegevensbereik om te controleren of het voldoet aan de criteria om in het totale bedrag te worden opgenomen.
4. "USA" - Criteria waarnaar moet worden gezocht in het opgegeven gegevensbereik ( D2:D16 )
5. ) – Sluithaakje dat het einde van de formule aangeeft.

De formule toont een totale omzet van $ 6.050 die is gemaakt voor producten die vanuit het magazijn in de VS zijn verzonden.

GEMIDDELDENIFS

Uitsplitsing formule:

1. =GEMIDDELDE.ALS( – De “=” geven het begin van de formule aan.
2. E2:E16 – Verwijst naar het gegevensbereik waarvan we het gemiddelde willen nemen. In dit voorbeeld willen we het gemiddelde aantal verkopen krijgen voor alle telefoons die in de VS zijn verkocht.
3. D2:D16 – Verwijst naar het gegevensbereik om te controleren of het voldoet aan de criteria die moeten worden opgenomen in de gemiddelde formule.
4. "USA" – Criteria waarnaar moet worden gezocht in het opgegeven gegevensbereik
5. ) – Sluithaakje dat de uiteinden van de formule aangeeft.

De formule laat zien dat we het product in de VS hebben verkocht voor ongeveer $ 1.008 per telefoon.

Alle drie de formules kunnen meer dan één criterium bevatten. Als we bijvoorbeeld dezelfde cijfers willen weten (d.w.z. AANTAL , SOM en GEMIDDELDE ) voor producten die in de VS worden verkocht , maar specifiek alleen voor het  merk Samsung , hoeven we alleen het te controleren gegevensbereik toe te voegen, gevolgd door de criteria.

Zie het voorbeeld hieronder waar een tweede criterium wordt toegevoegd aan de initiële criteriacontroles. (Blauwe tekst geeft het eerste criterium aan en rood geeft het tweede criterium aan)

=AANTAL.ALS(D2:D16,"VS", B2:B16,"Samsung") =SOMMEN.ALS(E2:E16;D2:D16,"VS", B2:B16,"Samsung") =GEMIDDELDE.ALS(E2:E16;D2 :D16,"VS", B2:B16,"Samsung")

U zult merken dat Excel ook  formules AANTAL.ALS , SOM.ALS en GEMIDDELDE.ALS heeft zonder het achtervoegsel "S" . Deze worden op dezelfde manier gebruikt als AANTALLEN. , SOMMEN.ALS en GEMIDDELDEN.ALS . Die zonder het achtervoegsel "S" in de formule hebben echter de beperking om slechts één criterium per formule toe te staan.

Omdat de syntaxis iets anders is, raad ik aan om  alleen COUNTIFS , SUMIFS en AVERAGEIFS te gebruiken, aangezien deze indien nodig voor één of meer criteria kunnen worden gebruikt. Genieten van!