Matrixformel? Summe(wenn(A2:a10;in Liste enthalten

R_o_b · 1. Mai 2011 um 12:59

Hallo Profis,

ich habe eine Wertetabelle, aus der ich bestimmte Werte einer Spalte summieren möchte. Die Kriterien sind: ist der Wert rechts daneben =„ja“, ist der Wert links daneben =‚in Liste auf anderem Tabellenblatt enthalten‘

Beispiel:

Orange 12 gekauft 
Apfel 4 offen
Apfel 3 gekauft
Pfirsich 5 gekauft
Birne 2 gekauft
Birne 3 offen
Apfel 5 offen

Jetzt möchte ich die Summe aller gekauften Früchte, die in dieser Liste mit dem definierten Bereich „GutesObst“ enthalten sind:

Apfel
Orange

Die Matrix-Formel müßte also in etwa lauten

{=SUMME(WENN(ISTNV(SVERWEIS(A2:A100;ZweitesSheet.xlsx!GutesObst;1;0));B2:B10;0))}

Funktioniert so aber nicht. Warum? Wie könnte es klappen?

Vielen Dank und einen schönen ersten Mai :o)
-Rob.

Reinhard_fb8cb7 · 1. Mai 2011 um 13:29

ich habe eine Wertetabelle, aus der ich bestimmte Werte einer
Spalte summieren möchte. Die Kriterien sind: ist der Wert
rechts daneben =„ja“, ist der Wert links daneben =‚in Liste
auf anderem Tabellenblatt enthalten‘
Jetzt möchte ich die Summe aller gekauften Früchte, die in
dieser Liste mit dem definierten Bereich „GutesObst“ enthalten
sind:

Hallo Rob,

mit Hilfsspalte:

Tabellenblatt: [Mappe1]!Tabelle1
 │ A │ B │ C │ D │ E │ F │
──┼──────────┼────┼─────────┼───┼───┼───────┤
1 │ Orange │ 12 │ gekauft │ 0 │ 5 │ Apfel │
──┼──────────┼────┼─────────┼───┼───┼───────┤
2 │ Apfel │ 4 │ offen │ 1 │ │ Birne │
──┼──────────┼────┼─────────┼───┼───┼───────┤
3 │ Apfel │ 3 │ gekauft │ 1 │ │ │
──┼──────────┼────┼─────────┼───┼───┼───────┤
4 │ Pfirsich │ 5 │ gekauft │ 0 │ │ │
──┼──────────┼────┼─────────┼───┼───┼───────┤
5 │ Birne │ 2 │ gekauft │ 1 │ │ │
──┼──────────┼────┼─────────┼───┼───┼───────┤
6 │ Birne │ 3 │ offen │ 1 │ │ │
──┼──────────┼────┼─────────┼───┼───┼───────┤
7 │ Apfel │ 5 │ offen │ 1 │ │ │
──┴──────────┴────┴─────────┴───┴───┴───────┘
Benutzte Formeln:
D1: =ZÄHLENWENN(GutesObst;A1)
D2: =ZÄHLENWENN(GutesObst;A2)
D3: =ZÄHLENWENN(GutesObst;A3)
D4: =ZÄHLENWENN(GutesObst;A4)
D5: =ZÄHLENWENN(GutesObst;A5)
D6: =ZÄHLENWENN(GutesObst;A6)
D7: =ZÄHLENWENN(GutesObst;A7)
E1: =SUMMENPRODUKT((C1:C100="gekauft")\*(D1:smiley:100=1)\*B1:B100)


Festgelegte Namen:
GutesObst: =Tabelle1!$F$1:blush:F$2

A1:F7
haben das Zahlenformat: Standard

Tabellendarstellung erreicht mit dem Code in FAQ:2363

Gruß
Reinhard

R_o_b · 1. Mai 2011 um 16:03

Keine Lösung ohne Hilfsspalte? Danke :o)
Salu Reinhard,

ohne Hilfsspalte und ohne Makros also nix zu machen? Schade.

Trotzdem eine Tolle Antwort. SUMMENPRODUKT hatte ich überhaupt nicht mehr in Erinnerung. Ermöglicht auch in diversen anderen Konditional-Summen den Verzicht auf Matrix-Formeln.

Mensch, Reinhard, vielen Dank. Magst Du nicht mal einen Flattr-Button oder ähnliches hier anbieten? Ich würde so gerne (wie schon vor einiger Zeit mal angesprochen) durch eine kleine Spende helfen, Dich als kompetenten Helfer hier zu unterstützen…

Einstweilen einen schönen Mai-Sontag :o)
-Rob.

Reinhard_fb8cb7 · 1. Mai 2011 um 16:20

Lösung ohne Hilfsspalte.
Hallo Rob,

ohne Hilfsspalte und ohne Makros also nix zu machen? Schade.

ich habe nochmal geschaut:

Tabellenblatt: [Mappe1]!Tabelle1
 │ A │ B │ C │ D │ E │
──┼──────────┼────┼─────────┼────┼────────┤
1 │ Orange │ 12 │ gekauft │ 14 │ Orange │
──┼──────────┼────┼─────────┼────┼────────┤
2 │ Apfel │ 4 │ offen │ │ Birne │
──┼──────────┼────┼─────────┼────┼────────┤
3 │ Apfel │ 3 │ gekauft │ │ │
──┼──────────┼────┼─────────┼────┼────────┤
4 │ Pfirsich │ 5 │ gekauft │ │ │
──┼──────────┼────┼─────────┼────┼────────┤
5 │ Birne │ 2 │ gekauft │ │ │
──┼──────────┼────┼─────────┼────┼────────┤
6 │ Birne │ 3 │ offen │ │ │
──┼──────────┼────┼─────────┼────┼────────┤
7 │ Apfel │ 5 │ offen │ │ │
──┴──────────┴────┴─────────┴────┴────────┘
Benutzte Formeln:
D1: =SUMMENPRODUKT((C1:C100="gekauft")\*(ZÄHLENWENN(GutesObst;A1:A100))\*B1:B100)

Festgelegte Namen:
GutesObst: =Tabelle1!$E$1:blush:E$2

A1:E7
haben das Zahlenformat: Standard

Tabellendarstellung erreicht mit dem Code in FAQ:2363

Mensch, Reinhard, vielen Dank. Magst Du nicht mal einen
Flattr-Button oder ähnliches hier anbieten? Ich würde so gerne
(wie schon vor einiger Zeit mal angesprochen) durch eine
kleine Spende helfen, Dich als kompetenten Helfer hier zu
unterstützen…

Tja nun, Geld könnte ich echt wahrlich gut gebrauchen

Gruß
Reinhard

Iacta_alea_est_2f01c3 · 1. Mai 2011 um 18:06

OT: Keine Lösung ohne Hilfsspalte? Danke :o)

Mensch, Reinhard, vielen Dank. Magst Du nicht mal einen
Flattr-Button oder ähnliches hier anbieten? Ich würde so gerne

Diese Frage ist im falschen Brett , lob und kritik an wer-weiss-was .

(wie schon vor einiger Zeit mal angesprochen) durch eine
kleine Spende helfen, Dich als kompetenten Helfer hier zu
unterstützen…

Also am Button kanns nicht liegen …
Wer wirklich kompetente Hilfe bezhalen will, findet einen Weg .

Ich habs jedenfalls immer geschafft. Ist halt nur fair was abzugeben für fremde arbeit . Einmal allerdings kam die Antwort das keine Spende gewünscht wird, das ist dann halt so und ist auch gut so.

P.S. schreib doch mal eine email und tauscht euch aus.

Hans_Bartelme · 1. Mai 2011 um 19:03

Hallo-Lass Dich mal mit dem Formelassistenten durch die Funktion SUMMEWENNS (beachte das S am Ende) begleiten.

Einen schönen Abend noch. (nein danke, Spende nicht nötig )

Reinhard_fb8cb7 · 1. Mai 2011 um 20:32

Hallo Hans,

Hallo-Lass Dich mal mit dem Formelassistenten durch die
Funktion SUMMEWENNS (beachte das S am Ende) begleiten.

ich glaube meine Glaskugel ist veraltet, welche Glaskugel-Version benutzt du denn um zu wissen daß Rob XL2007 oder höher hat?

(nein danke, Spende nicht nötig )

Schön für dich.

Gruß
Reinhard

R_o_b · 1. Mai 2011 um 22:39

Salu Reinhard,

ich habe nochmal geschaut:

Tabellenblatt: [Mappe1]!Tabelle1
│ A │ B │ C │ D │ E │
──┼──────────┼────┼─────────┼────┼────────┤
1 │ Orange │ 12 │ gekauft │ 14 │ Orange │
──┼──────────┼────┼─────────┼────┼────────┤
2 │ Apfel │ 4 │ offen │ │ Birne │
──┼──────────┼────┼─────────┼────┼────────┤
3 │ Apfel │ 3 │ gekauft │ │ │
──┼──────────┼────┼─────────┼────┼────────┤
4 │ Pfirsich │ 5 │ gekauft │ │ │
──┼──────────┼────┼─────────┼────┼────────┤
5 │ Birne │ 2 │ gekauft │ │ │
──┼──────────┼────┼─────────┼────┼────────┤
6 │ Birne │ 3 │ offen │ │ │
──┼──────────┼────┼─────────┼────┼────────┤
7 │ Apfel │ 5 │ offen │ │ │
──┴──────────┴────┴─────────┴────┴────────┘
Benutzte Formeln:
D1:
=SUMMENPRODUKT((C1:C100=„gekauft“)*(ZÄHLENWENN(GutesObst;A1:A10
0))*B1:B100)

Festgelegte Namen:
GutesObst: =Tabelle1!$E$1:blush:E$2

A1:E7
haben das Zahlenformat: Standard

Hmm, mir schien schon die erste Formel richtig zu sein. Muß über der hier noch mal grübeln. Danke Dir schon mal…

Tja nun, Geld könnte ich echt wahrlich gut gebrauchen

Ja nu, wie machenwers!? ;o)
Sei bestlich gegrüßt
-Rob.