SUMMENPRODUKT: Zahlenmuster erkennen

Bild

Betrifft: SUMMENPRODUKT: Zahlenmuster erkennen
von: Showd0wn
Geschrieben am: 08.06.2015 11:06:36

Hallo,
ich habe eine Spalte mit Unternehmensnamen, denen jeweils eine Spalte mit deren Postleitzahlen und eine Spalte mit der jeweiligen Unternehmenskategorien gegenübersteht.
Ich möchte jetzt die Anzahl aller Unternehmen deren PLZ mit 61... beginnt und bis 65... geht in der jeweiligen Kategorien bestimmen. Ich weiß nicht wie ich das mit dem ..., also einer beliebigen Abfolge der letzten 3 Zahlen hinbekomme und dann auch noch die PLZ von 61-65 verknüpfe.
Bisher habe ich dafür habe ich folgende Formel:
=SUMMENPRODUKT((PLZ=61118)*(Kategorie=L19))
PLZ & Kategorie sind jeweils die Namen der Spalten und L19 ist in diesem Fall die erste Kategorie, z.B. Software.
Ich habe das irgendwie mit Sternchen probiert: =SUMMENPRODUKT((PLZ="61*")*(Kategorie=L19)), aber das hat nicht hingehauen.
Wie bekomme ich das mit den PLZ hin? Also das nach allem gesucht wird, was mit 61, 62, 63, 64 und 65 beginnt?
Beste Grüße

Bild

Betrifft: (--LINKS(PLZ;2)=61)*... Gruß owT
von: Luc:-?
Geschrieben am: 08.06.2015 11:24:18
:-?

Bild

Betrifft: AW: SUMMENPRODUKT: Zahlenmuster erkennen
von: Klexy
Geschrieben am: 08.06.2015 11:27:10
In der ersten Formel hast du die PLZ als Zahl formatiert und angesprochen. Das ist nicht ok, denn es gibt Postleitzahlen, die mit 0 anfangen und deshalb formatiert man PLZ immer als Text.
In der zweiten Formel hast du die PLZ in Anführungszeichen, also als Text angesprochen. Das funktioniert nicht, wenn die PLZ tatsächlich im Zahlenformat formatiert sind.
Die Formel muss dann so lauten:

=SUMMENPRODUKT((LINKS(PLZ;2)="61")*(Kategorie=L19))


Bild

Betrifft: hier muss ich widersprechen ...
von: der neopa C
Geschrieben am: 08.06.2015 16:46:01
Hallo Klexy,
... und zwar Deiner getroffene Aussage: "deshalb formatiert man PLZ immer als Text".
Es gibt - auch schon in früheren Excel-Versionen - das Sonderformat PLZ , dass man für PLZ auch einsetzen sollte. Unabhängig davon könnte man auch mit einem benutzerdefinierten Zahlenformat arbeiten (für Deutschland eben einfach mit 00000).
Gruß Werner
.. , - ...

Bild

Betrifft: AW: hier muss ich widersprechen ...
von: Klexy
Geschrieben am: 08.06.2015 18:27:23
Das stimmt, aber in der Praxis, z.B. Serienbriefe, ist man damit manchmal gelöffelt. Und wenn man international unterwegs ist, hat man auch Postleitzahlen, die Buchstaben enthalten. Deshalb nehme ich immer Textformat und empfehle es auch so. Kostet nicht mehr, ist aber fehlerresistenter.

Bild

Betrifft: hast Du das Sonderformat schon mal genutzt? ...
von: der neopa C
Geschrieben am: 08.06.2015 18:36:54
Hallo,
... außerdem kannst Du für Textausgabezwecke bei Bedarf immer noch die Funktion TEXT() nutzen. Ich halte dies für weniger fehleranfällig und derartig formatierte PLZ vor allem viel besser und einfacher für Auswertungen jeglicher Art geeignet.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: hast Du das Sonderformat schon mal genutzt? ...
von: Klexy
Geschrieben am: 08.06.2015 18:49:01
Hab ich schon, aber mit einigem Ärger. Daher hab ich mich für die radikale Hausfrauenlösung entschieden. Bei Auswertungen hast du natürlich recht, da sind Zahlen einfacher, aber da mach ich mir zur Not eine Hilfsspalte, die die PLZ als Zahlen ausgibt. Mit PLZ ist es eh immer eine Frickelei, vor allem wenn man fremde Daten oder Exporte aus SAP oder sonstwo kriegt.

Bild

Betrifft: nun ...
von: der neopa C
Geschrieben am: 08.06.2015 19:18:37
Hallo,
... wenn es für Dich so günstiger ist, warum nicht. Aber grundsätzlich würde ich da schon eher die Mittel von Excel ausschöpfen
Gruß Werner
.. , - ...

Bild

Betrifft: AW: nun ...
von: Showd0wn
Geschrieben am: 09.06.2015 14:35:43
vielen Dank für die Antworten. Hat mir sehr weitergeholfen!

Bild

Betrifft: SUMMENPRODUKT: PLZ von bis
von: Rudi Maintaire
Geschrieben am: 08.06.2015 11:32:06
Hallo,
=SUMMENPRODUKT((PLZ>=61000)* (PLZ<=65999)*(Kategorie=L19))
Gruß
Rudi

Bild

Betrifft: einfacher und schneller mit ZÄHLENWENNS() ...
von: der neopa C
Geschrieben am: 08.06.2015 11:32:26
Hallo,
... für PLZ zwischen 61000 und 66000, wobei ich voraussetze, dass es echte PLZ-ZAHLEN sind und keine Textwerte (dann wird die Formel ein klein wenig anders)

=ZÄHLENWENNS(PLZ;">61000";PLZ;"<66000";Kategorie;L19)
Gruß Werner
.. , - ...

Bild

Betrifft: AW: einfacher und schneller mit ZÄHLENWENNS() ...
von: Klexy
Geschrieben am: 08.06.2015 13:25:11
Wie gesagt: PLZ sollten aber grundsätzlich Textwerte sein.

Bild

Betrifft: siehe dazu oben im thread owT
von: der neopa C
Geschrieben am: 08.06.2015 16:46:50
Gruß Werner
.. , - ...

Bild

Betrifft: AW: siehe dazu oben im thread owT
von: Showd0wn
Geschrieben am: 09.06.2015 14:35:49
vielen Dank für die Antworten. Hat mir sehr weitergeholfen!

 Bild

Beiträge aus den Excel-Beispielen zum Thema "SUMMENPRODUKT: Zahlenmuster erkennen"