SummeWenn-Funktion
Frank Bendrich
ich habe in Zeile 1 jeweils im Wechsel einfache Zahlen und Währungswerte. Also z.B.:
A1 = 1
B1 = 15,00
C1 = 4
D1 = 12,36
Die Währungszellen sind auch als Währung formatiert und die Zahlenzellen als Zahlen.
Nun möchte ich, dass als Summe nur die Währungen bzw. die Zahlenwerte zusammengezählt werden.
Ich wollte das irgenwie mit der SummeWenn-funktion realiesieren, bekomme das aber nicht gebacken.
Über Hilfe würde ich mich sehr freuen !
Grüße Frank
Philipp von Wartburg
Ich habe eine Lösungvariante, die . Vermutlich gibt es aber bessere.
In Zelle A2 die Formel
=ZELLE("Format";A1)
eintragen und in die Zellen B2 bis D2 (bzw. letzte Spalte) kopieren.
In A2 bis D2 müssten jetzt diese Resultate stehen:
F0 W2 F0 W2
'F0' bedeutet Format Zahl ohne Dezimalstellen; 'W2' steht für
Standard-Währungsformat. Falls andere Formate verwendet werden,
dann müssen die nachstehenden Formeln entsprechend angepasst
werden.
Die Matrix-Formel (!) für die Zahlen lautet so:
=SUMME(WENN(A2:D2="F0";A1:D1))
und die Matrix-Formel für die Währungen so:
=SUMME(WENN(A2:D2="W2";A1:D1))
PS: Es ist leider nicht möglich, die ZELLE-Funktion innerhalb der
restlichen Formel zu benutzen, z.B.
=SUMME(WENN(ZELLE("Format";A1:D1)="F0";A1:D1))
weil das Bezug-Argument von ZELLE immer nur eine Zelladresse
verarbeitet, auch wenn ein Bereich als Bezug angegeben wird.
Gruss
Philipp
Frank Bendrich schrieb in Nachricht ...
Frank Bendrich
vielen Dank, dass Du Dich meines Problems angenommen hast. leider gibt es da gleich mehrere Probleme. wenn ich die Formel
=Zellle.... einfüge und kopiere, bringt mir diese zwei ergebnisse: Einmal "F0", wie von Dir beschrieben. Und das andere mal nicht
"W2", sondern ".2". Das größte Problem ist aber, dass ich mehrere Zeilen habe, die jeweils für sich selbst berechnet werden sollen.
Deshalb kann ich die 2. Zeile nicht als temporäre Zwischenstation benutzen. Mittlerweile versuche ich mir eine benutzerdefinierte
Funktion zu schreiben, da ich mit en Excel-Internen Funktionen auch nicht weitergekommen bin. Aber vielleicht gibt es sie ja doch,
die LÖSUNG.
Trotzdem recht herzlichen Dank für Deine Mühe !!!
Grüße aus Berlin Frank
Philipp von Wartburg
Übrigens bedeutet das ".2", dass die Zelle mit dem
Währungsformat "#.##0,00" belegt ist. Das "W2" steht
für "#.##0,00 DM;-#.##0,00 DM". Die Codes findest Du
auch in der Online-Hilfe unter "ZELLE()", sollten sie doch
noch benötigt werden.
Noch ein Hinweis:
Ich befürchte dass eine benutzerdefinierte Funktion hier nicht
sauber laufen wird (kommt aber auf die XL-Version an). Nach
"Format/Zelle" wird keine Recalculation ausgelöst, sodass
nach einer Zellformat-Änderung (von Zahl auf Währung) die
benutzerdef. Funktion nicht neu berechnet wird. Auf jeden
Fall muss die Funktion als Volatil definiert werden, was evtl.
hilft.
Gruss
Philipp
Frank Bendrich schrieb in Nachricht <#Y4QWq0BCHA.1092@tkmsftngp02>...
AS
wo ist eigentlich das Problem? Willst Du nur die Zeilen addieren, so genügt z.B.
=B1+D1
oder die ganzen Spalten, dann die Array-Formeln:
=SUMME((A1:A100)+(C1:C100))
=SUMME((B1:B100)+(D1:D100))
jeweils Eingabe mit Strg/Shift und Enter
oder auch Normal-Eingabe z.B.
=SUMMENPRODUKT((B1:B100)+(D1:D100))
Gruß Arnim
Philipp von Wartburg
Ich bin davon ausgegangen, dass die Zahlen/Währungen nicht in
regelmässigen Abständen wechseln. Sind die Werte tatsächlich
in Spalte A/C/E/G... bzw. B/D/F/H..., dann sind die von Dir vorge-
schlagenen Formeln absolut ausreichend. Eine kleine Änderung
hätte ich allerdings, damit die Formel auch bei vielen Spalten nicht
zu lang wird und nicht gross geändert werden muss:
=SUMME(WENN(REST(SPALTE(A1:Z1);1)=1;A1:Z1)) 'A,C,E,G...
=SUMME(WENN(REST(SPALTE(A1:Z1);2)=1;A1:Z1)) 'B,D,F,H...
Gruss
Philipp
AS schrieb in Nachricht <772c9af4.02052...@posting.google.com>...
Frank Bendrich
Aufgrund der Tatsache, dass es sich bei der zu berücksichtigen Zelle jeweils um die zweite Spalte in der jeweiligen Zeile handelt,
konnte ich eine zuverlässige Funktion schreiben, welche ich als Argument die Zeilen-Nr. sowie den ersten Spalten-Index (gerade oder
ungerade) übergebe.
Die Funktion sieht wie folgt aus:
Public Function Berechnung(Zeile As Integer, Wert As Integer)
Application.Volatile
Dim iLS%, dSumme#, x%
iLS = Cells(Zeile, 256).End(xlToLeft).Column - 1
If iLS < 2 Then Berechnung = "Wert!": Exit Function
If Wert = 1 Then
For x = 1 To iLS Step 2
dSumme = dSumme + Cells(Zeile, x).Value
Next x
Berechnung = Format(dSumme, "0.00")
Exit Function
ElseIf Wert = 2 Then
For x = 2 To iLS Step 2
dSumme = dSumme + Cells(Zeile, x).Value
Next x
Berechnung = Format(dSumme, "##,##0.00 ?")
Exit Function
Else
Berechnung = "Error!"
Exit Function
End If
End Function
Vielleicht kann die Funktion ja noch jemand anders gebrauchen.
Danke nochmal für Deine Hilfe und schönen Tag noch ...
Frank
Frank Bendrich
das Problem ist folgendes:
Ich möchte in jeder Zeile einzeln nur die jeweils 2. Spalte zusammenzählen. Da hatte ich die Formel "=A1+C1+E1+....."
genommen. Leider sind es aber zu viele Spalten und Excel meldet nur lapidar Formel zu lang!
Deshalb suchte ich eine formel, die nur jede zweite Spalte der jeweiligen Zeile zusamenzählt. Deshalb dachte ich an die Funktion
SummeWenn. Hatte ich aber nicht hinbekommen.
Deine Formeln beziehen sich jeweils nur auf ganze Spalten. Nutzt mir aber leider nichts.
Bei der Formel: =SUMME(WENN(REST(SPALTE(A1:Z1);1)=1;A1:Z1))
von Philipp erhalte ich als Ergebnis immer 0, obwohl Werte in den Spalten vorhanden sind.
Und bei der Formel: =SUMME(WENN(REST(SPALTE(A1:Z1);2)=1;A1:Z1))
summiert Excel alle Spalten zusammen, was ja zu einem falschen Ergebnis führt.
naja, die von mir oben gezeigt benutzerdefinierte Funktion läuft für mich shr zufriedenstellen, obwohl ich wirklich lieber gerne
eine Formel aus Excel-Formeln gehb hätte. aber egal, Hauptsache es funtzt.
Danke für eure Hilfe
Grüße Frank
AS
danke für Deinen Hinweis!
Hallo Frank,
es stimmt: Ich habe Deine Frage nicht richtig aufgefaßt!
Doch zur Ehrenrettung :-) von Philipp: Seine Formeln sind schon ok! Du
mußt nur statt"=1" - "=0" schreiben (kleiner Tippfehler).
Es ist schön, daß Du eine VBA-Lösung gefunden hast. Aber wenn - wie Du
schreibst - lieber eine Formel-Lösung hättest, dann nimm halt unsere
Array-Formeln:
=SUMME((REST(SPALTE(A1:H1);2)=1)*A1:H1)
und:
=SUMME((REST(SPALTE(A1:H1);2)=0)*A1:H1)
Eingabe mit Strg/Shift/Enter.
Du darfst natürlich die Spalten-Bereiche nur bis zu der Summen-Spalte
eingeben (Zirkel-Bezug).
Gruß Arnim
Frank Arendt-Theilen
angenommen die Werte stehen im Bereich A1:H1, dann folgende Formel:
=SUMMENPRODUKT(A1:H1;(REST(SPALTE(A1:H1);2)<>0)*1)
Anmerkung:
Es werden die Werte der Zellen A1, C1, E1, G1 addiert.
Sollen die Werte der Zellen B1, D1, F1, H1 addiert werden, dann
ersetze den Ungleich-Operator (<>) durch den Gleicheits-Operator (=).
MfG Frank
_____________________________________________________
Frank Arendt-Theilen, Microsoft MVP für Excel, Hameln
eMail: Thei...@t-online.de, Homepage: http://www.xl-faq.de
Frank Bendrich
ich habe eben alle vorgeschlagenen Formeln ausprobiert. Es klappt wirklich super!!
Danke für Eure Hilfe und einen schönen Abend noch !!!
Grüße aus Berlin
Frank