wake-up-neo.com

Excel - SUMIF INDEX und MATCH

Ich bleibe bei einer Sumif-Formel. Ich denke, wenn ich eine Mischung aus Index und Match verwendet hätte, könnte ich es zum Laufen bringen, aber ich habe es in der letzten Stunde nicht geschafft!

Ich muss aus einer Nummerntabelle abhängig von der Hausnummer und 2 Daten summieren. Zum Beispiel muss ich die Zahlen für Haus 1 zwischen den Datumsangaben 08-05-17 und 13-05-17 summieren.

Meine bisherige Erfahrung mit Index und Übereinstimmung besteht darin, dass ich nur jeden verwendet habe, um eine bestimmte Ziffer zu erhalten. 

 What I'm trying to sum

 Where the sum should go

excelindexingexcel-formulamatchbetween
2
Bake.G

OFFSET und INDIRECT sind flüchtig, dies ist mit INDEX/MATCH möglich

=SUMIFS(INDEX(Sheet1!$W:$AC,MATCH("House " & B4,Sheet1!$A:$A,0),0),Sheet1!$W$3:$AC$3,"<=" & $AE4,Sheet1!$W$3:$AC$3,">=" & $AD4)

Der 0 als drittes Kriterium im INDEX gibt alle Spalten in der vom MATCH ausgewählten Zeile zurück.

Diese Formel ist nicht flüchtig und nicht Array.

3
Scott Craner

Vorausgesetzt, dass:

  • Die erste Tabelle befindet sich auf Blatt1
  • Die Daten von Haus 1 in der ersten Tabelle befinden sich in Zeile 4
  • Der zweite Tisch befindet sich auf einem separaten Blatt
  • Die Daten von Haus 1 in der zweiten Tabelle befinden sich in Zeile 4

Diese Formel in Tabelle2, Zelle AG4 sollte funktionieren:

SUM(INDIRECT("Sheet1!R4C"&22+MATCH(AD4,Sheet1!W3:AC3,0),FALSE):INDIRECT("Sheet1!R4C"&22+MATCH(AE4,Sheet1!W3:AC3,0),FALSE))
0
nicogen

Ändern Sie die folgenden

=SUMIFS(OFFSET(B3:ZZ3,MATCH("house 1",E:E,0)-3,0),B3:ZZ3,">"&42863,B3:ZZ3,"<"&42868)

Ändern Sie "Haus 1" in eine Referenz.

Ändern Sie 42863 und 42868 in Verweise auf Start- und Enddatum. (Die Zahlen sind nur Datumsangaben in Zahlenformaten.)

Ändern Sie 3s in den Bereichen und die Übereinstimmungsformel in die Zeilennummer, die die Datumsangaben enthält.

Ändern Sie B3: ZZ3 in den Zeilenbereich Ihres Datums. Unter der Annahme, dass Sie die Daten kontinuierlich aktualisieren, können Sie Platz schaffen, indem Sie den Bereich vergrößern, wie in meinem Beispiel in der Spalte ZZ.

0
Nitesh Halai

Formel für AI4 mit der Bearbeitung von Arrays (normale Formel jedoch nicht CSE):

=SUMPRODUCT(Sheet1!W4:XX100*
  (Sheet1!A4:A100 = "House " & A4)*
  (Sheet1!W3:XX3 >= AD4)*
  (Sheet1!W3:XX3 <= AE4))

p.s. Index/Match Version sollte (angeblich) schneller sein, aber ich mag dieses Formular wegen seiner Lesbarkeit ...

0
A.S.H