[Excel] Zeilen für Summenprodukt automatisch ermitteln

[josef24]

Guten Tag an die Fachleute. Ich möchte u. a. Code so einrichten, das die nach meinem Wissen starre Eingrenzung der Spalte und Nummer von - bis nur ein richtiges Ergebnis in meinem Beispiel ergibt. Frage: kann man die ausgefüllten Zeilen nicht automatisch ermitteln lassen, so das das richtige Ergebnis aus Summenprodukt errechnet wird. Die Anzahl Zeilen verändert sich, und man muß ständig den Code händisch aktualisieren, was sehr lästig ist.
Ich lese immer was von letzter Zeile ermitteln, aber ich muss doch den Zeilenumfang genau difinieren?
Kann mir da vielleicht jemand weiter helfen? Bin hier etwas ratlos. Gruß Josef

        letztezeile = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        MsgBox letztezeile

Worksheets("TabStat").Range("b2").FormulaLocal = "=SUMMENPRODUKT(((ArbTab!$C$2:$C$20=""Herr"")+(ArbTab!$C$2:$C$20=""Frau""))/ZÄHLENWENN(ArbTab!$B$2:$B$20;ArbTab!$B$2:$B$20))"
              Worksheets("TabStat").Range("b2").Copy
              Worksheets("TabStat").Range("b2").PasteSpecial xlValues

 

[Zvoni]

Worksheets("TabStat").Range("b2").FormulaLocal = "=SUMMENPRODUKT(((ArbTab!$C$2:$C$" & LetzteZeile & "=""Herr"")+(ArbTab!$C$2:$C$" & LetzteZeile & "=""Frau""))/ZÄHLENWENN(ArbTab!$B$2:$B$" & LetzteZeile & ";ArbTab!$B$2:$B$" & LetzteZeile & "))"

 

[josef24]

Danke für die Mammutzeile/Code. Er weist aber leider noch auf einen "Laufzeitfehler 1004" , Anwendungs- oder Objektdefinierter Fehler hin. Kann man da noch bitte auf eine Korrektur hoffen. Es würde mir natürlich sehr weiter helfen. Beispieldatei zur Anschauung. Gruß Josef

 

[Zvoni]

With Worksheets("ArbTab")
    LetzteZeile = .Cells(.Rows.Count, 1).End(xlUp).Row
End With

anstatt deiner "letztezeile"/"msgbox"-zeile

 

[josef24]

Danke für die Unterstützung.
Ich habe es mal rein kopiert. Aber beim Ergebnis passt es noch nicht. Aktualisierte Datei angehängt. Gruß Josef

 

[josef24]

Danke, Fehler gefunden. Gruß Josef

 

[josef24]

Muss nochmal anklopfen. Hatte meine Kontrolle zu oberflächlich gemacht. Das Ergebnis ist doch nicht korrekt.
Vielleicht kann mal von den Experten jemand einen Blick darauf werfen. Als Ergebnis erhalte ich in der Tabelle den Wert #DIV/0! Folgender Kode hatte ich versucht. Danke und Gruß Josef

Worksheets("TabStat").Range("b3").FormulaLocal = "=SUMMENPRODUKT((ArbTab!$C$2:$C$" & LetzteZeile & "=""Herr"")/ZÄHLENWENN(ArbTab!$B$2:$B$" & LetzteZeile & ";ArbTab!$B$2:$B$" & LetzteZeile & "))"
'  /ZÄHLENWENN(ArbTab!$B$2:$B$1;ArbTab!$B$2:$B$15))"
            Worksheets("TabStat").Range("b3").Copy
            Worksheets("TabStat").Range("b3").PasteSpecial xlValues

 

[Zvoni]

#DIV/0 impliziert, dass dein Zählenwenn als Ergebnis 0 ergibt.
Versuch mal in der Tabelle direkt nur deine ZÄHLENWENN-Formel zum laufen zu bringen.

EDIT: Ich seh gerade, dass du in deiner Zählenwenn für beide Argumente die Spalte B angibst
https://support.office.com/de-de/article/zÄhlenwenn-funktion-e0de10c6-f885-4e71-abb4-1f464816df34

 

[Zvoni]

schau mal jetzt, ob es so ok ist.
Ich hab im Code ein paar sachen auskommentiert.
LetztZeile muss übrigens nur einmal ermittelt werden.

EDIT: Hier war übrigens ein Fehler drin:
Worksheets("TabStat").Range("b3").FormulaLocal = "=SUMMENPRODUKT((ArbTab!$C$2:$C$" & LetzteZeile & "=""Herr"")/ZÄHLENWENN(ArbTab!$B$2:$B$" & LetzteZeile & ";ArbTab!;$B$2:$B$" & LetzteZeile & "))"

Da ist ein Semikolon zu viel (falls du das rote erkennen kannst)

 

[josef24]

Danke vielmals. Hatte das Semikolon übersehen. Code fuktioniert optimal. Aber zugegeben, die anderen Code in die richtige Reihenfolge zu bringen war mir nicht gelungen. Nochmals Danke für deine Geduld und Gruß Josef