Hoe gebruik je COUNTIFS, SUMIFS, AVERAGEIFS in Excel

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

In dit artikel gaan we de basisprincipes van deze drie functies en hun relevante en nuttige tegenhangers bekijken: COUNTIFS, SUMIFS en AVERAGEIFS.

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

Excel COUNT, SUM en GEMIDDELDE

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

= AANTAL (E2: E16)

Aan de andere kant, om de totale hoeveelheid verkopen die we hebben gemaakt te krijgen, kunnen we de SOM formule zoals hieronder getoond:

= SUM (E2: E16)

Als laatste, om de gemiddelde verkoopcijfers te weten te komen die we voor alle telefoons hebben gemaakt, kunnen we de GEMIDDELDE formule zoals hieronder:

= GEMIDDELDE (E2: E16)

Het resultaat zou moeten zijn zoals hieronder:

COUNT, SOM en GEMIDDELDE formules werken alleen voor records waarvan de celwaarde in getalnotatie is. Elke record binnen het formulebereik (d.w.z.. E2: E16 in dit voorbeeld) wordt niet in het getalformaat genegeerd.

Zorg er dus voor dat alle cellen in de COUNT-, SUM- en AVERAGE-formule allemaal zijn opgemaakt als Aantal, niet Tekst. Probeer dezelfde formule te gebruiken, maar met E: E als het bereik in plaats van E2: E16. Het geeft hetzelfde resultaat als voorheen omdat het de kop negeert (d.w.z.. Verkoopprijs), die in tekstformaat is.

Nu, wat als we het aantal verkopen, het totale verkoopbedrag en het gemiddelde aantal verkopen per telefoon willen weten, alleen voor degenen die in de VS worden verkocht? Dit is waar COUNTIFS, SUMIFS en AVERAGEIFS een belangrijke rol spelen. Neem de onderstaande formule in acht:

COUNTIFS

Formuleanalyse:

= COUNTIFS ( - De “=” geeft het begin van een formule in de cel en aan COUNTIFS is het eerste deel van de Excel-functie die we gebruiken.
D2: D16 - Verwijst naar gegevensbereik om te controleren of het voldoet aan de criteria die moeten worden opgenomen in de telformule.
"VERENIGDE STATEN VAN AMERIKA" - Criteria waarnaar moet worden gezocht in het gespecificeerde gegevensbereik (D2: D16)
) - Sluitingsbeugel die het einde van de formule aangeeft.

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

SOMMEN.ALS

Formuleanalyse:

= SOMMEN.ALS ( - De “=” geeft opnieuw het begin van de formule aan.
E2: E16 - Verwijst naar gegevensbereik dat we zouden willen totaliseren, d.w.z. verkoopprijs in ons voorbeeld.
D2: D16 - Verwijst naar gegevensbereik om te controleren of het voldoet aan de criteria die moeten worden opgenomen in het totale bedrag.
"VERENIGDE STATEN VAN AMERIKA" - Criteria waarnaar moet worden gezocht in het gespecificeerde gegevensbereik (D2: D16)
) - Sluitingsbeugel die het einde van de formule aangeeft.

De formule wordt weergegeven $ 6.050 totale verkopen die zijn gemaakt voor producten die zijn verzonden vanuit het magazijn in de VS..

AVERAGEIFS

Formuleanalyse:

= AVERAGEIFS ( - De “=” geef het begin van de formule aan.
E2: E16 - Verwijst naar het bereik van gegevens dat we zouden willen gemiddelde. In dit voorbeeld willen we het gemiddelde aantal verkopen voor alle telefoons die in de VS worden verkocht.
D2: D16 - Verwijst naar gegevensbereik om te controleren of het voldoet aan de criteria die moeten worden opgenomen in de gemiddelde formule.
"VERENIGDE STATEN VAN AMERIKA" - Criteria waarnaar moet worden gezocht in het opgegeven gegevensbereik
) - Sluitingsbeugel die de uiteinden van de formule aangeeft.

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

Alle drie de formule kan meer dan één criterium hebben. Als we bijvoorbeeld dezelfde cijfers willen kennen (d.w.z.. COUNT, SOM en GEMIDDELDE) voor producten die worden verkocht in Verenigde Staten van Amerika, maar specifiek alleen voor de Samsung merk, we moeten alleen het te controleren gegevensbereik toevoegen, gevolgd door de criteria.

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

= COUNTIFS (D2: D16, "USA", B2: B16, "Samsung") = SOMMEN (E2: E16, D2: D16, "VS", B2: B16, "Samsung") = AVERAGEIFS (E2: E16, D2: D16, "USA", B2: B16, "Samsung")

U zult merken dat Excel dat ook heeft COUNTIF, SUMIF en AVERAGEIF formules zonder het achtervoegsel “S”. Die worden op dezelfde manier gebruikt COUNTIFS, SOMMEN.ALS en AVERAGEIFS. Maar die zonder het achtervoegsel “S” in de formule hebben de beperking om slechts één criterium per formule toe te staan.

Omdat de syntaxis enigszins verschilt, zou ik aanraden om te gebruiken COUNTIFS, SOMMEN.ALS en AVERAGEIFS alleen als het voor één of meer criteria kan worden gebruikt, indien nodig. Genieten!