Zählenwenn Groß- und Kleinschreibung

[MalteMobile]

Guten Morgen,

ich habe eine Spalte A und eine Spalte B. In Spalte A sind Unternehmens-IDs enthalten. In Spalte B ist die Unternehmens-Branche enthalten. Nun möchte ich ermitteln, wie viele unterschiedliche Unternehmen pro Branche enthalten sind. In dem Beispiel im Anhang benutze ich dazu u.a. die ZÄHLENWENN Formel, allerdings beachtet diese nicht Groß- und Kleinschreibung. Ich habe schon Lösungsvorschläge gefunden, allerdings habe ich es nicht geschafft, diese in meinem Beispiel umzusetzen.

In meinem Beispiel werden für die Branche "Maschinenbau" zwei Unternehmen gelistet: "abcd" & "ABCD", allerdings unterscheidet ZÄHLENWENN diese Unternehmen nicht und zählt somit nur ein Unternehmen.

Vielen Dank im Voraus!

[Peter]

Hallo,

versuch es mal so:

=SUMMENPRODUKT((A$2:A$10<>"")*(B$2:B$10=D2))

[MalteMobile]

Hallo,

versuch es mal so:

=SUMMENPRODUKT((A$2:A$10<>"")*(B$2:B$10=D2))

Ah mist, ich hätte erwähnen sollen, dass ein Unternehmen auch mehrmals vorkommen kann, allerdings nur einmal gezählt werden soll! Deshalb ist die Formel auch so komplex. Im Anhang habe ich das Beispiel nochmal erweitert. Gibt es Ideen?

Trotzdem danke @Peter!

[Peter]

Hallo,

dann ein Vorschlag mit einer Hilfsspalte:

Tabelle1

	A	B	C	D	E
1	Unternehmens-ID	Unternehmens-Branche	Doppelt	Unternehmens-Branche	Anzahl
2	abcd	Maschinenbau	1	Maschinenbau	2
3	efgh	Zeitung	1	Zeitung	1
4	ijkl	Krankenhaus	1	Krankenhaus	2
5	mnop	Krankenhaus	1
6	ABCD	Maschinenbau	1
7	abcd	Maschinenbau	2

Formeln der Tabelle

Zelle Formel C2 =SUMMENPRODUKT(N(IDENTISCH(A2;A$2:A2))) E2 =SUMMENPRODUKT(($B$2:$B$10=D2)*($C$2:$C$10=1))

[MalteMobile]

Hallo,

dann ein Vorschlag mit einer Hilfsspalte:

Tabelle1

	A	B	C	D	E
1	Unternehmens-ID	Unternehmens-Branche	Doppelt	Unternehmens-Branche	Anzahl
2	abcd	Maschinenbau	1	Maschinenbau	2
3	efgh	Zeitung	1	Zeitung	1
4	ijkl	Krankenhaus	1	Krankenhaus	2
5	mnop	Krankenhaus	1
6	ABCD	Maschinenbau	1
7	abcd	Maschinenbau	2

Formeln der Tabelle

Zelle Formel C2 =SUMMENPRODUKT(N(IDENTISCH(A2;A$2:A2))) E2 =SUMMENPRODUKT(($B$2:$B$10=D2)*($C$2:$C$10=1))

Super, das hat geklappt! Ich hätte dann noch eine Frage:

Wie sorge ich dafür, dass in Spalte D automatisch die Unternehemens-Branche eingetragen wird. D.h. wenn ich ein neues Unternehmen in Spalte A aus einer neuen Unternehmens-Branche hinzufüge, die Liste in Spalte D automatisch erweitert wird?

Vielen Dank!

[Peter]

Hallo,

ich würde die Auswertung mit einer Pivottabelle machen.

Die Spalten A bis C würde ich als 'intelligente Tabelle' formatieren über Einfügen - Tabelle. Das hat z.B. den Vorteil, dass die Formel in der Spalte C automatisch weitergeführt wird wenn weitere Einträge hinzukommen.

Der zweite Vorteil liegt darin, dass der Bereich für die PT dynamisch wird.

Um die PT zu erstellen gehst du wie folgt vor:

Eine Zelle in der Datentabelle markieren - Einfügen - Pivottable und dem Assistenten folgen.

Ziehe das Feld Doppelt in das Filterfeld. Das Feld Unternehmensbranche ziehst du in das Zeilenfeld und das Feld Unternehmens-ID in Werte. Filtere jetzt nach dem Wert 1 und du erhälst deine Auswertung.

Alternativ kannst du die Formel in der Hilfsspalte auch so ändern, dass dort nur Wahr oder Falsch als Ergebnis steht

=SUMMENPRODUKT(N(IDENTISCH(A2;A$2:A2)))=1

und jetzt filterst du einfach auf den Wert WAHR.

Wenn du bei deiner Formellösung bleiben willst findest du hier eine Anleitung:

http://www.herber.de/excelformeln und bitte suchen .../formeln.html?welcher=194

[Luffy]

Super, das hat geklappt! Ich hätte dann noch eine Frage:

Wie sorge ich dafür, dass in Spalte D automatisch die Unternehemens-Branche eingetragen wird. D.h. wenn ich ein neues Unternehmen in Spalte A aus einer neuen Unternehmens-Branche hinzufüge, die Liste in Spalte D automatisch erweitert wird?

Vielen Dank!

Hi,

probier mal das:

Code:

=WENNFEHLER(INDEX($B$2:$B$71;AGGREGAT(15;6;ZEILE($B$1:$B$70)/(($B$2:$B$71<>"")*(VERGLEICH($B$2:$B$71&"_";$B$2:$B$71&"_";0)=ZEILE($B$1:$B$70)));ZEILE(B1)));"")

[Jockel]

Hallo,wenn keine Leerzeilen in B vorkommen (also zwischen den Einträgen) geht auch ab D2..:

Code:

=INDEX(B:B;AGGREGAT(15;6;ZEILE(A$2:A33)/(ZÄHLENWENN(D$1:D1;B$2:B33)=0);1))&""

[MalteMobile]

Hallo,wenn keine Leerzeilen in B vorkommen (also zwischen den Einträgen) geht auch ab D2..:

Code:

=INDEX(B:B;AGGREGAT(15;6;ZEILE(A$2:A33)/(ZÄHLENWENN(D$1:D1;B$2:B33)=0);1))&""

Auch eine gute Lösung. Leider kann es vorkommen, dass die Branche nicht ausgefüllt ist.

[Jockel]

Auch eine gute Lösung. Leider kann es vorkommen, dass die Branche nicht ausgefüllt ist.

Hallo, dann ginge..:

Code:

=WENNFEHLER(INDEX(B:B;AGGREGAT(15;6;ZEILE(A$2:A33)/(($B$2:B33<>"")*(ZÄHLENWENN(D$1:D1;B$2:B33)=0));1));"")