In Zelle B5 habe ich eine Formel:
=SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10,A2:A10&"")-(COUNTIF(A2:A10,A2:A10&"")=1))
Wenn Spalte A die unten gezeigten Daten enthält, gibt die Formel die richtige Antwort 3 zurück, da es drei Werte gibt, die mehr als einmal vorkommen (14, 16 und 17). Das ist genau das, was ich wissen möchte: dass es drei Werte gibt, die mehr als einmal vorkommen. Ich muss weder wissen, was die Werte sind, noch wo sie sind (obwohl ich für den letzten Teil ein nettes Bit habe).
Column A:
12
13
14
14
14
15
16
16
17
17
Wenn sich jedoch irgendwo im Bereich A2:A10 eine leere Zelle befindet, wird die Anzahl der doppelten Werte um 1 verringert (im obigen Beispiel würde die Formel 2 zurückgeben, obwohl sie 3 sein müsste). In meinem Beispiel unten wird die leere Zelle durch „B“ dargestellt.
Column A:
12
13
14
B
14
15
16
16
17
17
In diesem Beispiel kommen 14, 16 und 17 immer noch jeweils mehr als einmal vor. Die Formel sollte also 3 zurückgeben, gibt aber 2 zurück.
Ich bin ziemlich sicher, dass dies am ersten Teil liegt, =SUMPRODUCT((A2:A10<>"")wo im Wesentlichen nicht leere Zellen gezählt werden. Wenn es zufällig zwei (oder mehr) leere Zellen gibt, wird natürlich erneut hochgezählt, aber das ist auch nicht wirklich richtig, weil immer noch ein nicht leeres Duplikat ausgelassen wird (falls das überhaupt Sinn ergibt).
Antwort1
Hinweis: Ihre Frage bezieht sich auf eine9Zellbereich, aber Sie zeigen10Werte in den Beispielen, also passt das nicht ganz zusammen … obwohl ich das Problem verstehe.
Der erste Teil Ihrer Formel ist in Ordnung, da dies die Standardmethode zum Zählen der Anzahl unterschiedlicher nicht leerer Werte ist. Wenn Sie jedoch den zweiten Teil subtrahieren, COUNTIFmüssen Sie auch Leerzeichen ausschließen, sodass Sie den Teil &"" im zweiten Teil entfernen müssen COUNTIF. Diese Version
=SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10,A2:A10&"")-(COUNTIF(A2:A10,A2:A10)=1))
...aber diese Version ist besser
=SUMPRODUCT((COUNTIF(A2:A10,A2:A10)>1)/COUNTIF(A2:A10,A2:A10&""))
Beide Formeln funktionieren entweder für Text oder numerische Daten in A2:A10 (oder eine Mischung aus beiden), aber für nur numerische Werte (wie in Ihrem Beispiel) können Sie auch FREQUENCYeine Funktion wie diese verwenden
=SUMPRODUCT((FREQUENCY(A2:A10,A2:A10)>1)+0)