Wie verwende ich zwischensumme mit if in excel?

DIESE FORMEL IST PURE MAGIE.

Wenn Sie in Excel eine ZWISCHENSUMME-Formel mit IF (Bedingung) schreiben möchten, müssen Sie dazu mehrere Funktionen verwenden. Aber bevor wir das tun, wollen wir die Daten verstehen, die wir für dieses Beispiel haben.

Im obigen Beispiel haben Sie drei Spalten:

• Name
• Alterstafel
• Geschlecht

Und wenn Sie eine Tabelle aus der Spalte „Alterstabelle“ filtern, wird die Anzahl der Frauen in Zelle F1 angezeigt. Das bedeutet also, dass wir eine Formel haben, die die Anzahl der gefilterten Werte anzeigt, jedoch mit einer Bedingung.

Die Formel, die wir haben:

 =SUMPRODUCT((C2:C41=E1)*(SUBTOTAL(3,OFFSET(C2,ROW(C2:C41)-MIN(ROW(C2:C41)),0))))

Die ZWISCHENSUMME IF-Formel verstehen

Diese Formel verwendet fünf Funktionen: SUMPRODUCT, SUBTOTAL, OFFSET, ROW und MAX. Um diese Formel zu verstehen, müssen wir sie daher in mehrere Teile aufteilen.

1.ZEILE(C2:C41)-2.0)

Dieser Teil der Formel verwendet die Funktionen MIN und ROW.

• In ROW haben wir auf die Spalten „Geschlecht“ verwiesen und es wird ein Array mit Zeilennummern zurückgegeben.
• Danach übernimmt MIN die Array- oder Zeilennummern und gibt die minimale Zeilennummer zurück. Deshalb haben wir in diesem Teil der Formel 2.

2.ZEILE(C2:C41)

In diesem Teil haben wir nur die ROW-Funktion, die ein Array von Zeilennummern zurückgibt.

3. OFFSET(C2,LINE(C2:C41)-MIN(LINE(C2:C41)),0)

Wir haben jetzt die OFFSET-Funktion. Es hilft Ihnen, einen Verweis auf einen Bereich zu erstellen, indem Sie einen Zellbezug als Ausgangspunkt verwenden. Im Referenzargument haben wir uns auf Zelle C2 bezogen, die erste Zelle, in der unser Geschlechtsbereich beginnt.

Im Linienargument haben wir diesen Teil der Formel, die oben in den ersten beiden Teilen besprochen wurde. Danach haben wir im Argument cols 0 verwendet. Mit all dem gibt OFFSET ein Array aller Werte in der Spalte „Sex“ zurück.

4. ZWISCHENSUMME(3,OFFSET(C2,LINE(C2:C41)-MIN(LINE(C2:C41)),0))

Wir haben das vom OFFSET zurückgegebene Array in der ZWISCHENSUMME verwendet. Und in function_num haben wir 3 verwendet, was SUBTOTAL anweist, die COUNTA-Funktion zur Berechnung zu verwenden.

Wenn Sie einen Filter für die Spalte „Altersgruppe“ verwenden, gibt dieser ZWISCHENSUMME-Teil der Formel ein Array mit 0 und 1 zurück.

In dieser Tabelle haben wir 1 für Werte, die dem Wert entsprechen, auf den wir den Filter angewendet haben. Siehe das Beispiel unten:

5. (C2:C41=E1)

Dieser Teil der Formel gibt durch Testen einer Bedingung ein Array zurück. In dieser Bedingung testen wir, ob der Bereichswert „Weiblich“ ist, und er gibt im Array TRUE und FALSE zurück.

In dieser Tabelle haben wir WAHR für den Wert „Weiblich“ und FALSCH für die anderen.

7. Letzter Teil

Am Ende haben wir zwei Tabellen in SUMPRODUCT. Und wir haben auch einen Sternchenoperator zwischen diesen Arrays.

Wenn wir die beiden Tabellen miteinander multiplizieren, erhalten wir eine einzige Tabelle mit 0 und 1. In dieser Tabelle steht eins (1) für den Wert „Weiblich“ im Geschlecht und 21-30 für „Altersgruppe“.

Am Ende gibt SUMPRODUCT die Summe mithilfe dieses Arrays zurück. Und diese Summe ist die Anzahl der Zellen mit dem Wert „Weiblich“ in der Spalte „Geschlecht“, wenn Sie in der Spalte „Altersgruppe“ nach 21–30 Jahren filtern.