Thread: Count direct duplicates only

I have a list of numbers running sequentially 1-X in a column. These numbers restart at random intervals (i.e. 1,2,3,4,1,2,3,4,5,6,1,2,3,1,2...) but sometimes have a repetition (i.e. 1,2,3,4,4,5,1,2,3,1,2,3,4,5,1,2,2,3....) etc.

I want to be able to count just the repetitions. In the last example, the value I would want returned would be 2 (the 4 & 2 repeated). How can I do this?

Thank you!

Welcome to the forum.

Suppose the data from your second example is in F1:F18.
This formula gives the result 2:

=SUMPRODUCT(--(F1:F17=F2:F18))

F1:F17 deliberately omits the last cell (F18).
F2:F18 deliberately omits the first cell (F1).

Hi,

For curiosity: what do the operators -- do in the expression?

Good question....

In Excel, when you use the equality operator (=) to compare two operands, the result is a boolean: True or False. For example, A1=A1 returns True. SUMPRODUCT() ignores boolean values because is designed to work with numbers, so we have to find a way to convert the boolean results (Trues and Falses) into numeric equivalents (1s and 0s). A trick to do this is to perform an arithmetic operation on the boolean result - any arithmetic operator will do:

(A1=A1)+0      returns 1
(A1=A1)*1      returns 1
--(A1=A1)      returns 1
(A1=A1)-0      returns 1
etc....

By performing arithmetic, Excel implictly understands that we're working with numbers and will return numeric results.


So which arithmetic operator do you use? Often you'll see people using two unary minuses because:
(1) the unary minus is high up in the operator precedence list
(2) it's fractionally faster to calculate than the alternatives

For more info, see here -
http://www.xtremevbtalk.com/showpost...33&postcount=5