>> Summe bei mehreren Abfragen je Spalte

[stoxxii]

Hey Guys,
Hey Admins,

ich habe ein Problem mit SummeWenns bei Abfrage mehrerer Kriterien in einer Spalte.

Bitte schaut kurz in den Scrrenshot im Anhang rein.

Ich will aus Spalte "A"  ein Kriterium abfragen :    F1*  oder F2*  oder HH4*  

und dann in Spalte "S" :  die 2 Kriterien :  "1" und das Wort "new".

Summe soll in Spalte "W"  gebildet werden.

Mit SummenProdukt kann das eventuell klappen ??

Danke für Tipps vorab.   
viele Grüße
stoxxii

[HKindler]

Hi,

ohne Beispieldatei bleibt's von meiner Seite bei einer theoretischen Ausführung ohne praktisches Beispiel mit Bezug auf deine Tabelle.

SUMMEWENNS() macht immer eine UND-Verknüpfung aller Bedingungen. Soll Spalte A addiert werden wenn in Spalte B der Wert 1 oder 2 auftritt, dann würdest du im ersten Moment an so etwas denken:
=SUMMEWENNS(A:A;B:B;1;B:B;2)
Daraus macht Excel aber: Summiere A, wenn in B der Wert 1 steht und wenn in B der Wert 2 steht. Das wird jedoch nie der Fall sein, da keine Zelle gleichzeitig 1 und 2 sein kann. Anders sieht es aus, wenn du das so formulierst:
=SUMMEWENNS(A:A;B:B;">=1";B:B;"<=2")
Das funktioniert wie gewünscht. zumindest solange in B nur ganze Zahlen und keine Kommazahlen auftauchen. Und auch nur, wenn du nicht 1 oder 3, aber nicht 2 addieren willst. Also im Grunde auch nicht brauchbar.

Wie von dir schon erwähnt, geht es mit SUMMENPRODUKT. Aber Vorsicht: hier sollte man keine kompletten Spalten verwenden, denn sonst wird Excel schnarchlahm.
Wie geht's? Man muss wissen, dass mathematisch eine Multiplikation einer UND-Verknüpfung und eine Addition einer ODER-Verknüpfung entspricht.
=SUMMENPRODUKT(A1:A10*((B1:B10=1)+(B1:B10=2)))
Würde schon mal das gewünschte liefern.
Man beachte: die Vergleiche müssen eingeklammert sein. Alles was ODER-Verknüpft wird, muss ebenfalls innerhalb einer klammer stehen, da sonst die Multiplikation zuvor ausgeführt würde.

Soll nun auch noch gleichzeitig in C ein x stehen, dann könnte man das so schreiben:
=SUMMENPRODUKT(A1:A10*((B1:B10=1)+(B1:B10=2))*(C1:C10="x"))

[schauan]

... die interessante Frage dabei wäre ja, wie das mit der neuen 1 gemeint ist ... Ich hätte da 5 Varianten in Vermutung :

Summe x, y und z bei *1*new*
Summe x, y und z bei *1*new* oder *new*1*
Summe x, y und z bei *1 new*
Summe x, y und z bei 1 new
Summe x, y und z bei 1 oder new

	A	B	C
1	1	x	1 too old
2	4	y
3	3	z	1 new
4	4	a
5	5	b	1 is very new
6	6	c
7	7	x	give me new 1 yet
8	8	y	was not new
9	9	z	more than 1 is not new yet

Verwendete Systemkomponenten: [Windows (64-bit) NT 10.00] / MS Excel 365

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

[Jockel]

Hey Guys,
Hey Admins,

ich habe ein Problem mit SummeWenns bei Abfrage mehrerer Kriterien in einer Spalte.

...
viele Grüße
stoxxii

Ich würde das mit einer Datenbankfunktion beackern...

[schauan]

... und die 6. Variante hab ich vergessen ...

Summe x, y und z bei *1* oder *new*

Ich tippe aber darauf, das die Varianten 5 und 6 eher nicht gemeint sind sondern 1 und new in einer Zelle vorkommen sollen, wie auch immer .. Variante 2 wäre aus meiner Sicht anhand der Beschreibung die wahrscheinlichste, also
Summe x, y und z bei *1*new* oder *new*1*

[stoxxii]

Hallo Helmut,

jetzt habe ich mir eine simple Excel Tabelle gebastelt mit 4 Spalten , wo in 2 Spalten mehrere Kriterien auftreten.

Aufgabe : wir suchen die Summe für das Budget in SPALTE "H", 

wenn folgendes Kriterium der SPALTE A  und das Mehrfach-Kriterium in Spalte D erfüllt wird.

Spalte A :  Nimm alle "AC1"  oder alle "B1" oder "B4"  oder oder   (hier nur 1 Kriterum)
 und gleichzeitig dazu in 

SPALTE D : die "1" und "new"   (2 Kriterien)
Ich gurke gerade auch mit SummenProdukt herum ...  

Danke für Tipps vorab.

viele Grüße
stoxxii

[steve1da]

Hola,
das wird immer 0 ergeben denn in Spalte D steht entweder 1 ODER new, nicht UND.
Gruß,
steve1da

[HKindler]

Hi,

=SUMMENPRODUKT(H6:H600*(LINKS(A6:A600;3)="AC1")*((D6:D600=1)+(D6:D600="new")))
=SUMMENPRODUKT(H6:H600*(LINKS(A6:A600;2)="B1")*((D6:D600=1)+(D6:D600="new")))

Oder wenn du dein "AC1" etc. in z.B. K4 schreibst:
=SUMMENPRODUKT(H6:H600*(LINKS(A6:A600;LÄNGE(K4))=K4)*((D6:D600=1)+(D6:D600="new")))

Solltest du in Spalte A mit dem "oder" ein echtes ODER gemeint haben:

Code:

=SUMMENPRODUKT(
    H6:H600
    *(
        (LINKS(A6:A600;3)="AC1")
        +(LINKS(A6:A600;2)="B1")
        +(LINKS(A6:A600;2)="B4")
    )
    *(
        (D6:D600=1)
        +(D6:D600="new")
    )
)

Hier mal etwas "übersichtlicher" dargestellt, damit man die Klammerebenen besser sehen kann.

[stoxxii]

vielen Dank Helmut 

könnte man nicht auch ein Wildcard Symbol einbauen :  A6 &"*"  oder so ähnlich

anstatt immer die Spaltenabfragen  mit  Links(..., 3) oder Links(..., 2 )   oder Links(....,4)  auszuschreiben...

weil ich möchte die Formel herunterziehen, über ca. 30 Zeilen  und  in diesen 30 Zeilen steht nur einmal das "Short Cut"
also die Abkürzung für unsere Häuser....

viele Grüße
stoxxii

[Egon12]

Hallo,

ich vermute mal, dass =TEILERGEBNIS() in O2016 schon drin ist.
Wenn ja und ich es richtig verstanden habe, nutze den Filter (Spalte A Teilstring) der Tabelle und dann =TEILERGEBNIS(109;H7:H10000).

Um jetzt keine Ausgabe zu erhalten, wenn der Filter nicht aktiv ist braucht es VBA. Da kann man mit einer UDF nachhelfen. 

Besser wäre Office 2021 oder 2024. Da kann man das so lösen:

Code:

=LET(xA;A6:H10000;
xB;FILTER(xA;ISTZAHL(SUCHEN($J$1;INDEX(xA;;1)));"");
xC;FILTER(xB;(INDEX(xB;;4)=1)+(INDEX(xB;;4)="new"));
SUMME(INDEX(xC;;8)))

In J1 steht derTeilstring, von dem die Summe berechnet wird.

Gruß Uwe