Summenprodukt und "Wenndann" kombinieren?!

[dennis1793]

Hallo Zusammen,

folgende Formel liegt vor:

=SUMMENPRODUKT(ISTZAHL(VERGLEICH('[Auswertung 2017 - 2020.xlsx]Gesamt'!$A$3:$A$272;{26340;26215;26345;26316};0))*'[Auswertung 2017 - 2020.xlsx]Gesamt'!$FHS$3:$FHS$272)

Aktuell muss ich die den roten Bereich der Formel noch händisch anpassen, dieses soll nun vereinfacht werden.

Die Formel soll so angepasst werden, dass diese die Spalte A und mit der Zeile 1 eines anderen Blatts (Auswertung 2017 - 2020.xlsx]Gesamt) überprüft und bei Übereinstimmung die darunterliegen Zahlen übernehmen soll. 

Wer kann mir helfen?

[Cadmus]

Die Antwortwahrscheinlichkeit sinkt bei Mehrfachthemen drastisch.
https://www.clever-excel-forum.de/thread-31467.html

[dennis1793]

Die Antwortwahrscheinlichkeit sinkt bei Mehrfachthemen drastisch.
https://www.clever-excel-forum.de/thread-31467.html

Kannst du mir weiterhelfen? Ich bin echt verzweifelt ://    

[Cadmus]

Ich weiß nur anhand der Formel, nicht was da wohin soll.

Eine Beispieldatei mit entsprechenden Erläuterungen würde evtl. helfen.

Die Datei(en) sollten vom Aufbau dem Original entsprechen.

Evtl. würde ich die Dateien auch per PowerQuery in eine neue Datei zusammenführen.

[dennis1793]

Ich weiß nur anhand der Formel, nicht was da wohin soll.

Eine Beispieldatei mit entsprechenden Erläuterungen würde evtl. helfen.

Die Datei(en) sollten vom Aufbau dem Original entsprechen.

Evtl. würde ich die Dateien auch per PowerQuery in eine neue Datei zusammenführen.

Hallo Cadmus,

vielen Dank, dass Du dir die Zeit nimmst und dir das noch einmal anschaust. 

Habe Dir eine Tabelle nachgebaut. In C4 ist die ursprüngliche Formatierung. A4 soll nun mit J1:N1 abgeglichen werden und die dementsprechenden Zahlen sollen übernommen werden.

Vielen Dank für Deine Hilfe! 

[Cadmus]

A4 soll nun mit J1:N1 abgeglichen werden

In J1 steht doch schon "Gesamt..." . ? Was soll da abgeglichen werden?

Ich würde ich das per Pivot machen. Dazu muss die Kreuztabelle vorher über PowerQuery entpivotiert werden.

Die betreffenden PLZ über den Filter auswählen.

[Fred11]

Hallo Dennis,

meinst du das vielleicht so ?

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C
1	Umsatz nach Radien
2
3	Warengruppe	0-10 km	2021
4	Gesamt Jan-Okt 2021	Gesamt	400
5	WG01 Jan-Okt  2021	WG01	399
6	WG02 Jan-Okt 2021	WG02	500
7	WG03 Jan-Okt 2021	WG03	333
8	WG04 Jan-Okt 2021	WG04	355

Zelle	Formel
C4	=SUMMENPRODUKT(ISTZAHL(VERGLEICH($I$2:$I$15;{26340;26215;26345;26316};0))*INDEX($J$2:$N$15;0;VERGLEICH(A4;$A$4:$A$8;0)))

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

siehe angehängte Beispieldatei

Gruß
Fred

[dennis1793]

In J1 steht doch schon "Gesamt..." . ? Was soll da abgeglichen werden?

Ich würde ich das per Pivot machen. Dazu muss die Kreuztabelle vorher über PowerQuery entpivotiert werden.

Die betreffenden PLZ über den Filter auswählen.

Guten Morgen, vielen Dank für Deine Antwort und Mühe! Das werde ich ausprobieren, muss mich erst nochmal mit PowerQuery auseinandersetzen 

---

Hallo Dennis,

meinst du das vielleicht so ?


Super! Danach habe ich gesucht! Vielen Dank Fred!

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C
1	Umsatz nach Radien
2
3	Warengruppe	0-10 km	2021
4	Gesamt Jan-Okt 2021	Gesamt	400
5	WG01 Jan-Okt  2021	WG01	399
6	WG02 Jan-Okt 2021	WG02	500
7	WG03 Jan-Okt 2021	WG03	333
8	WG04 Jan-Okt 2021	WG04	355

Zelle	Formel
C4	=SUMMENPRODUKT(ISTZAHL(VERGLEICH($I$2:$I$15;{26340;26215;26345;26316};0))*INDEX($J$2:$N$15;0;VERGLEICH(A4;$A$4:$A$8;0)))

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

siehe angehängte Beispieldatei

Gruß
Fred

[dennis1793]

Hallo Fred, 

habe nochmal eine Frage, wenn die Tabelle ausgeweitet werden soll:

Schaue Dir mal bitte meine Tabelle dazu an. Wenn die Formel nach rechts kopiert werden wird, dann passen die Werte nicht 

Wo liegt hier das Problem? 


Danke und Gruß

[Cadmus]

Der Vergleichsbereich ist falsch.

Sollen auch noch andere Entfernungen als 0-10km ausgewertet werden? Gibt es eine Entfernungstabelle nach PLZ?
Mit PQ und Pivot auch dann kein Problem und ohne Formeln ;)
Als Beipiel: