Excel: Summenprodukt mit INDIREKT

[RockyRonny]

Hallo an alle,

für folgende Formel benötige ich Hilfe:

=(SUMMENPRODUKT(--('Tabelle2'!$G$7:$G$80=$B7)*(--('Tabelle2'!$U$7:$AX$80=K$6))))/$F$2

Die Formel wird in Tabelle1 berechnet und bezieht sich auf Daten aus Tabelle2 und funktioniert auch. Der fett geschriebene Teil soll nun jedoch über die INDIREKT Funktion dynamisch gestaltet sein, da sich der Bereich wöchentlich ändert. Den dynamischen Bereich möchte in B3 in Tabelle1 vorgeben.

Sollte dieser Fall schon behandelt werden, gerne darauf verweisen. Meine Suche ergab keine Treffer.

Vielen Dank!
RockyRonny

 

[floq0r]

Und wo liegt dein Problem wenn du INDIREKT nutzt?

 

[RockyRonny]

Ich weiß nicht, wie ich es einbauen muss ehrlich gesagt😅

 

[floq0r]

Wie kommst du dann auf INDIREKT und wer hat die obige Formel geschrieben? Und wenn du es nicht warst wieso macht es nicht der?

Probier mal =(SUMMENPRODUKT(--('Tabelle2'!$G$7:$G$80=$B7)*(--(INDIREKT("'Tabelle2'!"&Tabelle1!$B$3)=K$6))))/$F$2 und in Tabelle1!B3 dann $U$7:$AX$80

 

[RockyRonny]

Die Formel ist tatsächlich von mir, weiter kam ich mit meinem Excel-Wissen nur leider nicht und ich dachte mir, das müsste doch mit INDIREKT gehen Habs versucht, aber bekam entweder #Bezug oder #Wert..

Deine Formel funktioniert, besten Dank für die schnelle Lösung!

 

[RPP63]

Verständnisfrage:

Den dynamischen Bereich möchte in B3 in Tabelle1 vorgeben.

Was natürlich genauso "dynamisch" ist, als wenn Du die Zellformel änderst.
Mal ein Beispiel mit "echter" Dynamik:
B1: Überschrift
B2:Bx: Liste, die erweitert wird
dynamischer Bereich:
=B2:INDEX(B:B;ANZAHL2(B:B))

 

[RockyRonny]

Was natürlich genauso "dynamisch" ist, als wenn Du die Zellformel änderst.

Ja da hast du vollkommen Recht Die Formel ist in insgesamt 650 Zellen drin und daher der gewünschte Zellbezug, damit in allen Zellen der Bezug geändert wird.

Vielleicht kurz zum Verständnis:
Als Quelldaten habe ich einen Einsatzplan, in dem in Spalte A die Mitarbeiter von oben nach unten aufgelistet sind und in den Spalten dann die Tage fortlaufend aufgelistet sind (aufgeteilt in vor- und nachmittags). Da die MAs zwischen 10 Standorten wechseln, wird in den entsprechenden Zellen eine Zahl von 1-10 eingetragen. Ich möchte mir ausgeben, wie häufig der MA in einem bestimmten Zeitraum (der in B3 angegeben wird) am jeweiligen Standort war, um einen Standortschlüssel zu erhalten.

Jetzt gebe ich in B3 die auszuwertenden Spaltenbreite an, hier im Beispiel $U$7:$AX$80. Was mir noch helfen könnte, ist eine mögliche Eingabe "von...bis...", also der INDIREKT Bezug auf zwei Zellen.

Edit: Konnte es selbst lösen nochmals Danke an alle

 

[Schweinchen59]

Da es bei mir um Indirekt geht, wollt ich den Tread noch mal zum Leben erwecken.
Folgende Formel greift:
=SUMMEWENNS(A13:A8577;B13:B527;"H";C13:C527;B7)

Da aber zur Kontrolle nicht immer alle Positionen zu Summieren sind, wollte ich die Formel etwas Dynamischer gestalten und habe folgendes eingebunden:
=SUMMEWENNS(INDIREKT(A3):INDIREKT(A4);B13:B527;"H";C13:C527;B7)

In A3 bzw. A4 stehen jeweils die Referenzen für Anfangszeile und Endzeile enthalten.

Spalte A	Spalte B
B3	A13
B4	A8577

(klar kann man noch das A vereinfachen, aber Verfeinerungen kommen erst wenn alles funktioniert)
Leider bekomme ich bei dieser (banalen) Formel immer ein “#WERT!“ ausgegeben.

Wer bitte kann helfen?

 

[cumulonimbus8]

Blindschuss - muss das Ergebnis von Indirekt explizit zu einer Zeichenfolge gemacht werden?
(Mir klappt zu vieles Unlogische und zu vieles Logische nicht in diesen Formeln…)

CN8

 

[floq0r]

Ein Tipp: Wenn du einen Teil einer Formel markierst und F9 drückst wird nur der markierte Teil ausgewertet (STRG+Z für undo), das erleichtert debugging

 

[Schweinchen59]

Danke für den Hinweis.
=INDIREKT(A3) alleine in einer Zelle ergibt im o.g. Beispiel A13 -- was richtig wäre. Im Zusammenspiel mit der Funktion summewenns greift indirkt aber nicht, was genau mein Problem ist.

 

[floq0r]

Ich glaube wie @cumulonimbus8 schon bemerkt hat musst du den Verweis als String aufbauen, d.h. INDIREKT(A3) & ":" & INDIREKT(A4) (ungetestet)

 

[Schweinchen59]

Habe ich schon versucht, geht leider nicht, da summewens zwei Werte erwartet und nicht nur einen String. Der String ist inhaltlich richtig, wird aber von summewenns nicht verarbeitet.

 

[steve1da]

Hola,

Folgende Formel greift:
=SUMMEWENNS(A13:A8577;B13:B527;"H";C13:C527;B7)

die Formel kann nicht greifen, weil Summewenns() zwingend gleich große Bereiche verlangt. Diese Formel ergibt #WERT ebenso alle Versuche mit Indirekt, weil die Bereiche eben nicht gleich groß sind.
Korrekt wäre:
=SUMMEWENNS(INDIREKT(B3&":"&B4);B13:B527;"H";C13:C527;B7)
Aber auch nur dann, wenn in B3 "A13" steht und in B4 "A527".
Gruß
steve1da

 

[Schweinchen59]

Hallo Steve, Du hast recht, ich hatte in meiner Musterformel nur Teile kopiert, in der Originalformel sind alle Bereiche gleich groß.
Deine Formel funktioniert bei mir, ich werde sie mir noch genauer anschauen, Dankr für die tolle Hilfe.

 

[steve1da]

Wobei man dann natürlich alle Bereiche mit Indirekt() erzeugen lassen muss, sonst ist das ganze ja witzlos 😊
Edit: wenn du nur die Zeilennummern in B3 und B4 schreibst, könnte man das mit Index() an Stelle von Indirekt() schnell für alle Bereiche machen.