So verwenden Sie COUNTIFS, SUMIFS, AVERAGEIFS in Excel

Die drei am häufigsten verwendeten Formeln in Excel, die einfache mathematische Berechnungen durchführen, sind COUNT , SUM und AVERAGE . Egal, ob Sie ein Finanzbudget in Excel verwalten oder einfach nur Ihren nächsten Urlaub im Auge behalten, Sie haben wahrscheinlich schon einmal eine dieser Funktionen verwendet.

In diesem Artikel werden wir die Grundlagen dieser drei Funktionen und ihre relevanten und nützlichen Gegenstücke durchgehen: COUNTIFS, SUMIFS und AVERAGEIFS.

Nehmen wir an, wir starten ein neues Online-Geschäft, das Mobiltelefone verkauft, und wir haben ein Blatt, das die Verkäufe auflistet, die wir in den ersten zwei Monaten getätigt haben. Laden Sie hier eine Beispiel-Excel-Tabelle herunter .

Excel ANZAHL, SUMME und DURCHSCHNITT

Um zu wissen, wie viele Mobiltelefone wir verkauft haben, können wir schnell die COUNT- Formel verwenden, wie unten gezeigt:

=ZAHL(E2:E16)

Andererseits können wir, um den Gesamtbetrag der von uns getätigten Verkäufe zu erhalten, die SUM- Formel wie unten gezeigt verwenden:

=SUMME(E2:E16)

Um schließlich die durchschnittlichen Verkäufe zu ermitteln, die wir für alle Telefone getätigt haben, können wir die AVERAGE- Formel wie folgt verwenden:

=MITTELWERT(E2:E16)

Das Ergebnis sollte wie folgt aussehen:

Die Formeln COUNT, SUM und AVERAGE funktionieren nur für Datensätze, bei denen der Zellenwert im Zahlenformat vorliegt. Jeder Datensatz innerhalb des Formelbereichs (dh E2:E16 in diesem Beispiel), der nicht im Zahlenformat vorliegt, wird ignoriert.

Stellen Sie daher bitte sicher, dass alle Zellen in den Formeln COUNT, SUM und AVERAGE als Number und nicht als Text formatiert sind . Versuchen Sie, dieselbe Formel zu verwenden, aber mit E:E als Bereich anstelle von E2:E16 . Es wird das gleiche Ergebnis wie zuvor zurückgeben, da es den Header (dh Sale Price ) ignoriert, der im Textformat vorliegt.

Was ist nun, wenn wir die Anzahl der Verkäufe, die Gesamtsumme der Verkäufe und die durchschnittliche Anzahl der Verkäufe pro Telefon wissen möchten, nur für die in den USA verkauften? Hier spielen COUNTIFS, SUMIFS und AVERAGEIFS eine wichtige Rolle. Beachten Sie die folgende Formel:

ZÄHLENWENNS

Formelaufschlüsselung:

1. =ZÄHLENWENNS( – Das „=“ zeigt den Beginn einer Formel in der Zelle an und ZÄHLENWENNS ist der erste Teil der Excel-Funktion, die wir verwenden.
2. D2:D16 – Bezieht sich auf einen Datenbereich, der überprüft werden muss, um festzustellen, ob er die Kriterien erfüllt, die in die Zählformel aufgenommen werden sollen.
3. „USA“ – Kriterien, nach denen im angegebenen Datenbereich gesucht werden soll ( D2:D16 )
4. ) – Schließende Klammer, die das Ende der Formel anzeigt.

Die Formel gibt 6 zurück, was die Anzahl der Verkäufe für Produkte ist, die aus dem Lager in den USA versandt werden.

SUMME

Formelaufschlüsselung:

1. =SUMIFS( – Das „=“ zeigt wieder den Anfang der Formel an.
2. E2:E16 – Bezieht sich auf den Datenbereich, den wir summieren möchten, dh in unserem Beispiel den Verkaufspreis.
3. D2:D16 – Bezieht sich auf einen Datenbereich, der überprüft werden muss, um festzustellen, ob er die Kriterien erfüllt, die in den Gesamtbetrag aufgenommen werden sollen.
4. „USA“ – Kriterien, nach denen im angegebenen Datenbereich gesucht werden soll ( D2:D16 )
5. ) – Schließende Klammer, die das Ende der Formel anzeigt.

Die Formel zeigt einen Gesamtumsatz von 6.050 USD für Produkte, die aus dem Lager in den USA versandt wurden.

DURCHSCHNITTSF

Formelaufschlüsselung:

1. =AVERAGEIFS( – Das „=“ zeigt den Anfang der Formel an.
2. E2:E16 – Bezieht sich auf einen Datenbereich, den wir mitteln möchten. In diesem Beispiel möchten wir den durchschnittlichen Umsatz aller in den USA verkauften Telefone erhalten.
3. D2:D16 – Bezieht sich auf einen Datenbereich, der überprüft werden muss, um festzustellen, ob er die Kriterien erfüllt, die in die Durchschnittsformel aufgenommen werden sollen.
4. „USA“ – Kriterien, nach denen im angegebenen Datenbereich gesucht werden soll
5. ) – Schließende Klammer, die die Enden der Formel anzeigt.

Die Formel zeigt, dass wir das Produkt in den USA für rund 1.008 US-Dollar pro Telefon verkauft haben.

Alle drei Formeln können mehr als ein Kriterium annehmen. Wenn wir beispielsweise dieselben Zahlen (dh COUNT , SUM und AVERAGE ) für in den USA verkaufte Produkte wissen möchten , aber speziell nur für die  Marke Samsung , müssen wir nur den zu prüfenden Datenbereich gefolgt von seinen Kriterien hinzufügen.

Bitte sehen Sie sich das folgende Beispiel an, in dem ein zweites Kriterium zu den anfänglichen Kriterienprüfungen hinzugefügt wird. (Blauer Text zeigt das erste Kriterium an und rot zeigt das zweite Kriterium an)

=ZÄHLENWENNS(D2:D16,"USA", B2:B16,"Samsung") =SUMMEFS(E2:E16,D2:D16,"USA", B2:B16,"Samsung") =MITTELWERTWENNS(E2:E16,D2 :D16,"USA", B2:B16,"Samsung")

Sie werden feststellen, dass Excel auch  ZÄHLENWENN , SUMMEWENN und MITTELWERTWENN Formeln ohne das Suffix „S“ hat . Diese werden ähnlich wie COUNTIFS , SUMIFS und AVERAGEIFS verwendet . Diejenigen ohne das Suffix „S“ in der Formel haben jedoch die Einschränkung, dass nur ein Kriterium pro Formel zulässig ist.

Da die Syntax etwas anders ist, würde ich empfehlen , nur COUNTIFS , SUMIFS und AVERAGEIFS zu verwenden  , da sie bei Bedarf für ein oder mehrere Kriterien verwendet werden können. Genießen!