Clever-Excel-Forum › Office-Anwendungen › Excel v
SUMMENPRODUKT mit wechselnder Spalte im Bereich

Thema geschlossen 
Baumstrukturmodus
SUMMENPRODUKT mit wechselnder Spalte im Bereich
schauan Offline
Einer von hier
Registriert seit: 10.04.2014
Version(en): 97-2019 (32) + 365 (64)
#1
13.04.2021, 17:34
Hallöchen,

kann man das mit einer Formel lösen und wenn ja, wie ? Es soll eine bedingte Summe gebildet werden aus den Inhalten unterschiedlicher Spalten. Welche Spalte in einer Zeile des Bereichs betroffen ist wird hier in Spalte A festgelegt. Mit INDEX kann man ja einen Bereich zurückgeben, allerdings richtet der sich hier im Muster immer nach dem Inhalt von Zelle A1 aus und nicht nach der jeweiligen Zeile , also für Zeile 1 nach A1, Zeile 2 nach A2 usw. Sad Die Lösung soll ohne Hilfsspalten, Zwischenergebnisse, Makro o.ä. auskommen, das schaff ich selber Smile ...

Arbeitsblatt mit dem Namen 'Tabelle1'
ABCDEFGHIJKL
11x5123456789
24xSoll456789012
378789012345

ZelleFormel
C1=SUMMENPRODUKT((B1:B3="x")*(INDEX(D1:L3;;A1:A3)))
Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016
Diese Tabelle wurde mit Tab2Html (v2.6.2) erstellt. ©Gerd alias Bamberg
.      \\\|///      Hoffe, geholfen zu haben.
       ( ô ô )      Grüße, André aus G in T  
  ooO-(_)-Ooo    (Excel 97-2019+365)
Homepage Suchen
Top
shift-del Offline
{Irrtum / Versuch * Glaskugel}
Registriert seit: 12.04.2014
Version(en): Office 365
#2
13.04.2021, 18:00
Moin

Du hast das Zeilenargument ausgelassen.
Setze dort {1;2;3} ein.
Wir sehen uns!
... Detlef

Meine Beiträge können Ironie oder Sarkasmus enthalten.

Homepage Suchen
Top
schauan Offline
Einer von hier
ThreadStarter
Registriert seit: 10.04.2014
Version(en): 97-2019 (32) + 365 (64)
#3
13.04.2021, 18:10
Hallöchen,

da bekomme ich 2 raus Sad (Excel 2019)
.      \\\|///      Hoffe, geholfen zu haben.
       ( ô ô )      Grüße, André aus G in T  
  ooO-(_)-Ooo    (Excel 97-2019+365)
Homepage Suchen
Top
shift-del Offline
{Irrtum / Versuch * Glaskugel}
Registriert seit: 12.04.2014
Version(en): Office 365
#4
13.04.2021, 18:31
Arbeitsblatt mit dem Namen 'Tabelle1'
ABCDEFGHIJKL
11x8123456789
24x8456789012
37789012345

ZelleFormel
C1=SUMMENPRODUKT((B1:B3="x")*(INDEX(D1:L3;{1;2;3};A1:A3)))
C2=SUMME(INDEX(D1:L3;{1;2;3};A1:A3)*(B1:B3="x"))
Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016
Diese Tabelle wurde mit Tab2Html (v2.6.2) erstellt. ©Gerd alias Bamberg
Wir sehen uns!
... Detlef

Meine Beiträge können Ironie oder Sarkasmus enthalten.

Homepage Suchen
Top
schauan Offline
Einer von hier
ThreadStarter
Registriert seit: 10.04.2014
Version(en): 97-2019 (32) + 365 (64)
#5
13.04.2021, 18:36 (Dieser Beitrag wurde zuletzt bearbeitet: 13.04.2021, 18:50 von schauan.)
... kann ich auch Smile bzw. Sad

Arbeitsblatt mit dem Namen 'Tabelle1'
ABCDEFGHIJKL
11x2123456789
24xSoll456789012
378789012345
42

ZelleFormel
C1=SUMMENPRODUKT((B1:B3="x")*(INDEX(D1:L3; {1;2;3};A1:A3)))
C4{=SUMME(INDEX(D1:L3;{1;2;3};A1:A3)*(B1:B3="x"))}
Achtung, Matrixformel enthalten!
Die geschweiften Klammern{} werden nicht eingegeben.
Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.
Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016
Diese Tabelle wurde mit Tab2Html (v2.6.2) erstellt. ©Gerd alias Bamberg
Unter 365 ist es ganz verrückt.

Wenn ich die Datei öffne, wird die Formel in C1 etwas angepasst:

=SUMMENPRODUKT((B1:B3="x")*(INDEX(D1:L3;@{1;2;3};@A1:A3)))
Ergebnis nach wie vor 2, da kann ich F9 tanzen oder was auch immer.

Bearbeite ich die Formel zu
=SUMMENPRODUKT((B1:B3="x")*(INDEX(D1:L3;{1;2;3};A1:A3)))
erhalte ich 8, ok

kopiere ich die Formel
=SUMMENPRODUKT((B1:B3="x")*(INDEX(D1:L3;@{1;2;3};@A1:A3)))
in eine andere Zelle, z.B. C5, erhalte ich #WERT

Das glaubt einem doch keiner ...


   
.      \\\|///      Hoffe, geholfen zu haben.
       ( ô ô )      Grüße, André aus G in T  
  ooO-(_)-Ooo    (Excel 97-2019+365)
Homepage Suchen
Top
shift-del Offline
{Irrtum / Versuch * Glaskugel}
Registriert seit: 12.04.2014
Version(en): Office 365
#6
13.04.2021, 18:59
Ich hätte noch ein paar Variationen.

Code:
=SUMME(INDEX(D1:L3;{1;2;3};WENN(1;A1:A3))*(B1:B3="x"))
=SUMME(WENNFEHLER(INDEX(D1:L3;{1;2;3};WENN(B1:B3="x";A1:A3;""));0))
Wir sehen uns!
... Detlef

Meine Beiträge können Ironie oder Sarkasmus enthalten.

Homepage Suchen
Top
schauan Offline
Einer von hier
ThreadStarter
Registriert seit: 10.04.2014
Version(en): 97-2019 (32) + 365 (64)
#7
13.04.2021, 19:31
Hallöchen,

danke erst mal. Unter 2019 erhalte ich damit auch die 2. Die Formeln muss ich zum Teil als Arrayformel eingeben, sonst kommt bei einigen #WERT - außer, ich gebe die in Zeile 1 ein. In Zeile 1 kommen die gleichen Ergebnisse auch ohne Arrayformelabschluss.

Arbeitsblatt mit dem Namen 'Tabelle1'
ABCDEFGHIJKLMNOPQ
11x21234567892221
24xSoll456789012
378789012345
42
52
62
71

ZelleFormel
C1{=SUMME(INDEX(D1:L3;{1;2;3};A1:A3)*(B1:B3="x"))}
N1=SUMMENPRODUKT((B1:B3="x")*(INDEX(D1:L3;({1;2;3});(A1:A3))))
O1=SUMMENPRODUKT((B1:B3="x")*(INDEX(D1:L3;{1;2;3};A1:A3)))
P1=SUMMENPRODUKT((B1:B3="x")*(INDEX(D1:L3;{1;2;3};A1:A3)))
Q1=SUMME(INDEX(D1:L3;{1;2;3};WENN(1;A1:A3))*(B1:B3="x"))
C4{=SUMMENPRODUKT((B1:B3="x")*(INDEX(D1:L3;({1;2;3});(A1:A3))))}
C5{=SUMMENPRODUKT((B1:B3="x")*(INDEX(D1:L3;{1;2;3};A1:A3)))}
C6{=SUMME(INDEX(D1:L3;{1;2;3};WENN(1;A1:A3))*(B1:B3="x"))}
C7{=SUMME(WENNFEHLER(INDEX(D1:L3;{1;2;3};WENN(B1:B3="x";A1:A3;""));0))}
Achtung, Matrixformel enthalten!
Die geschweiften Klammern{} werden nicht eingegeben.
Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.
Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016
Diese Tabelle wurde mit Tab2Html (v2.6.2) erstellt. ©Gerd alias Bamberg
.      \\\|///      Hoffe, geholfen zu haben.
       ( ô ô )      Grüße, André aus G in T  
  ooO-(_)-Ooo    (Excel 97-2019+365)
Homepage Suchen
Top
shift-del Offline
{Irrtum / Versuch * Glaskugel}
Registriert seit: 12.04.2014
Version(en): Office 365
#8
13.04.2021, 20:18
Dann versuche noch mal diese Variante:
Code:
=SUMME(INDEX(D1:L3;WENN(1;{1;2;3});WENN(1;A1:A3))*(B1:B3="x"))
Wir sehen uns!
... Detlef

Meine Beiträge können Ironie oder Sarkasmus enthalten.

[-] Folgende(r) 1 Nutzer sagt Danke an shift-del für diesen Beitrag:
  • schauan
Homepage Suchen
Top
Fred11 Offline
Benutzer
Registriert seit: 12.01.2020
Version(en): 2010, 2021
#9
13.04.2021, 21:01
Hallo André

ich habe auch noch eine Variante - ganz ohne INDEX....:

Arbeitsblatt mit dem Namen 'Tabelle1'
ABCDEFGHIJKL
1Ergebnis13
2
31x123456789
44x456789012
57789012345
63x345678901

ZelleFormel
C1=SUMMENPRODUKT(($B$3:$B$6="x")*(SPALTE($D$3:$L$6)-SPALTE($C3)=$A$3:$A$6)*$D$3:$L$6)
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Gruß
Fred
[-] Folgende(r) 1 Nutzer sagt Danke an Fred11 für diesen Beitrag:
  • schauan
Suchen
Top
schauan Offline
Einer von hier
ThreadStarter
Registriert seit: 10.04.2014
Version(en): 97-2019 (32) + 365 (64)
#10
13.04.2021, 22:43
Hallo zusammen,

also, erst mal Danke allen zusammen.
So wie es aussieht funktioniert die Lösung von Fred unter 2019. Unter 365 funktionieren alle Vorschläge - kann ich jetzt nur nicht darstellen Sad Hier nochmal die Darstellung aus 2019 ergänzt um die beiden letzten Vorschläge.

Arbeitsblatt mit dem Namen 'Tabelle1'
ABCDEFGHIJKLMNOPQRS
11x2123456789221118
24xSoll456789012
378789012345
42
52
62
71
82
98

ZelleFormel
C1{=SUMME(INDEX(D1:L3;{1;2;3};A1:A3)*(B1:B3="x"))}
N1=SUMMENPRODUKT((B1:B3="x")*(INDEX(D1:L3;({1;2;3});(A1:A3))))
O1=SUMMENPRODUKT((B1:B3="x")*(INDEX(D1:L3;{1;2;3};A1:A3)))
P1=SUMME(INDEX(D1:L3;{1;2;3};WENN(1;A1:A3))*(B1:B3="x"))
Q1=SUMME(WENNFEHLER(INDEX(D1:L3;{1;2;3};WENN(B1:B3="x";A1:A3;""));0))
R1=SUMME(INDEX(D1:L3;WENN(1;{1;2;3});WENN(1;A1:A3))*(B1:B3="x"))
S1=SUMMENPRODUKT(($B$1:$B$3="x")*(SPALTE($D$1:$L$3)-SPALTE($C1)=$A$1:$A$3)*$D$1:$L$3)
C4{=SUMMENPRODUKT((B1:B3="x")*(INDEX(D1:L3;({1;2;3});(A1:A3))))}
C5{=SUMMENPRODUKT((B1:B3="x")*(INDEX(D1:L3;{1;2;3};A1:A3)))}
C6{=SUMME(INDEX(D1:L3;{1;2;3};WENN(1;A1:A3))*(B1:B3="x"))}
C7{=SUMME(WENNFEHLER(INDEX(D1:L3;{1;2;3};WENN(B1:B3="x";A1:A3;""));0))}
C8{=SUMME(INDEX(D1:L3;WENN(1;{1;2;3});WENN(1;A1:A3))*(B1:B3="x"))}
C9=SUMMENPRODUKT(($B$1:$B$3="x")*(SPALTE($D$1:$L$3)-SPALTE($C1)=$A$1:$A$3)*$D$1:$L$3)
Achtung, Matrixformel enthalten!
Die geschweiften Klammern{} werden nicht eingegeben.
Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.
Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016
Diese Tabelle wurde mit Tab2Html (v2.6.2) erstellt. ©Gerd alias Bamberg
.      \\\|///      Hoffe, geholfen zu haben.
       ( ô ô )      Grüße, André aus G in T  
  ooO-(_)-Ooo    (Excel 97-2019+365)
Homepage Suchen
Top
Thema geschlossen 


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste