Microsoft Excel

Das Portal zum Thema Excel-Formeln

Gruppe: Zählfunktionen

Beitrag: in wie vielen Zeilen (nicht Zellen) kommt ein Suchbegriff vor *

Aufgabe
Du willst wissen, in wie vielen Zeilen ein Begriff vorkommt.
Da der Begriff aber in mehreren Spalten vorkommen kann, genügt die normale ZÄHLENWENN Funktion nicht.

       A               B               C               D       
1      
2     x  
3        
4      
5        
6  
7        
8 Ergebnis  4     

Lösung
Tabelle: A1:D99
Suchbegriff: x

{=SUMME(WENN(ZÄHLENWENN(BEREICH.VERSCHIEBEN(A1:D1;ZEILE(1:99)-1;);"x");1))}
oder mit Summenprodukt:
=SUMMENPRODUKT(1*(ZÄHLENWENN(BEREICH.VERSCHIEBEN(A1:D1;ZEILE(1:99)-1;);"x")>0))
Alternative ohne BEREICH.VERSCHIEBEN mit SUCHEN und HÄUFIGKEIT:
=SUMMENPRODUKT((HÄUFIGKEIT(ISTZAHL(SUCHEN("x";A1:D99))*ZEILE(X1:X99);ISTZAHL(SUCHEN("x";A1:D99))*ZEILE(X1:X99))>0)*1)-1
wenn die Spaltenzahl gesucht wird:
=SUMMENPRODUKT((HÄUFIGKEIT(ISTZAHL(SUCHEN("x";A1:K19))*SPALTE(A1:K1);ISTZAHL(SUCHEN("x";A1:K19))*SPALTE(A1:K1))>0)*1)-1


Komplizierter ist es, wenn mehrere Suchbegriffe in den Zeilen gefunden werden sollen. a b und c (dürfen auch mehrfach vorkommen) sollen in A1 bis H9 gefunden werden :

       A               B               C               D               E               F               G               H               I       
1     a  b      c    1  
2 *b            0  
3     b  c    a    1  
4   ba    0  
5                 0  
6       0  
7               0  
8   c  b  ö  a  1  
9       abc      0  
10 Ergebnis:  3               


Mit Hilfsspalte ist es relativ einfach. In I1 steht:
=(ANZAHL(VERGLEICH({"a"."b"."c"};A1:H1;0))=3)*1
runterkopieren
Das Ergebnis ist dann:
=SUMME(I:I)

Das funktioniert aber auch mit ner solo-Formel (Chris Kaiser):
=SUMME(1*(MMULT((ZÄHLENWENN(BEREICH.VERSCHIEBEN(A1:H1;ZEILE(1:9)-1;;);{"a"."b"."c"})>0)*1;{1;1;1})=3))

Dürfen die Suchbegriffe nur einmal auftauchen, in der Formel >0 durch =1 ersetzen.