Summenbetrag für einen Bereich, wenn die jeweilige Zeile die Kriterien erfüllt

Question

Ich versuche, eine Tabelle zu erstellen, um die Summe der Stunden für jeden Monat basierend auf den jeweiligen Zeilen zu berechnen. Um das Beispiel etwas klarer zu machen: Für Excel unten sollte ich den Wert 28 für Kunde 1 und 16 für Kunde 3 erhalten.

Ich versuche, die Funktion SUMMEWENN zu verwenden, aber der Summenbereich kann sich nicht auf mehrere Zellen erstrecken.

Und so möchte ich die Informationen sehen:

Answer 1

Sie können eine einfache Formel erstellen:

=SUM(IF(B5:B21="Client 1",G5:AK21))

Das bedeutet, dass eine Array-Berechnung in zwei Schritten durchgeführt wird:

1. Gibt GESAMTE ZEILEN (auch Leerzeichen) zurück, wenn Spalte B = „Kunde 1“.
2. Sobald ich alle Zeilen habe (von denen die meisten Leerzeichen enthalten), summieren Sie einfach diese Werte.

Sie können in der Formel bei Bedarf durch einen Verweis auf eine externe Zelle ersetzen "Client 1". Stellen Sie außerdem sicher, dass der gesamte Monatsbereich im zweiten Teil der Formel korrekt ist G5:AK21.

Answer 2

Aufbauend auf dem exzellenten =SUM(IF())Ansatz von@Davees ist möglich, dieOFFSETFunktion zum Auswählen der Spalten, die den einzelnen 12 Monaten des Jahres entsprechen.

Die OFFSETFunktion definiert einen rechteckigen Bereich durch seine Höhe und Breite, wobei die obere linke Ecke des Bereichs durch die Anzahl der Zeilen unterhalb und Spalten rechts einer angegebenen Arbeitsblattzelle definiert ist. Im folgenden Ansatz verwende ich die gelb hervorgehobene Zelle als angegebene Zelle (Zelle E17).

Bereich A2:E14(siehe Screenshot unten) liefert einige Informationen, oderMetadaten, wo sich die Spalten mit den Tageswerten für jeden der 12 Monate des Jahres befinden, relativ zur hervorgehobenen Zelle. Die nützlichen Werte befinden sich in den Spalten mit der ÜberschriftCol_OffsetUndSäulenSo beginnen beispielsweise für Januar die Tageswerte in der 1. Spalte rechts neben der gelben Zelle und belegen 31 Spalten, für Februar beginnen die Tageswerte in der 32. Spalte rechts neben der gelben Zelle und belegen 29 Spalten (wenn das Jahr in der Zelle auf ein Nicht-Schaltjahr geändert wird, B1ändert sich der Wert in der Zelle E4auf 28), usw. Die Spalten mit der ÜberschriftMonat#UndKumulativsind Zwischenwerte erforderlich, um zu berechnenCol_OffsetUndSäulen.

Die Formel zur Zusammenfassung der Datensummen nach Monat und Kunde kann in einem Aufschlüsselungsformat wie folgt geschrieben werden:

=SUM(
IF(Sheet1!$B$18:$B$23=$A2,
OFFSET(Sheet1!$E$17,
1,
VLOOKUP(B$1,Sheet1!$A$3:$E$14,4),
6,
VLOOKUP(B$1,Sheet1!$A$3:$E$14,4)
)
)
)

oder als vollständige Formel

=SUM(IF(Sheet1!$B$18:$B$23=$A2,OFFSET(Sheet1!$E$17,1,VLOOKUP(B$1,Sheet1!$A$3:$E$14,4),6,VLOOKUP(B$1,Sheet1!$A$3:$E$14,5))))

wobei davon ausgegangen wird, dass die Metadaten und Tagesdaten im Arbeitsblatt enthalten sind Sheet1.

Diese Formel gilt für Januar/Kunde 1 und kann in die übrigen Zellen für andere Monate und Kunden kopiert werden, wie im Screenshot unten teilweise gezeigt. Stellen Sie sicher, dass die in den Metadaten und der Ergebnistabelle verwendeten Monatsnamen genau übereinstimmen, um #N/AFehler in den Ergebnissen zu vermeiden.

Das zweite und vierte Argument der OFFSETFunktion sind 1 und 6, da die Daten in der ersten Zeile nach der gelb markierten Zelle beginnen und im gezeigten Beispiel 6 Datenzeilen vorhanden sind. Wenn die Anzahl der Zeilen unterschiedlich ist, ersetzen Sie einfach den korrekten Wert.

Das dritte und fünfte Argument verwenden einfach die Funktion VLOOKUPzum Nachschlagen derCol_OffsetUndSäulenWerte in den Metadaten des betreffenden Monats.