1

In cell B5 I have a formula: 

=SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10,A2:A10&"")-(COUNTIF(A2:A10,A2:A10&"")=1))

If column A has the data as shown below, the formula returns the correct answer of 3, because there are three values which have more than one occurrence (14, 16 and 17). This is exactly what I want to know; that there are three values which appear more than once. I needn't know what the values are, nor where they are (although I do have a nifty bit for that last part). 

Column A: 
12
13
14
14
14
15
16
16
17
17

However, if anywhere in the range of A2:A10 is a blank cell, then the count of duplicate values if decremented by 1 (in the example above, the formula would return 2, when it should be 3) In my sample below, blank cell is represented by "B".

Column A:
12
13
14
B
14
15
16
16
17
17

In this example, 14, 16 and 17 still each occur more than one time; so the formula should return 3, but it returns 2.

I'm fairly certain this is due to the first portion =SUMPRODUCT((A2:A10<>"") Where it's counting essentially non-empty cells. Of course, if there happens to be two (or more) blank cells, than it re-increments, but this isn't really right either, because it's still omitting a non-blank duplicate (if that makes any sense at all).

Improve this question

1 Answer 1

Reset to default
1

Note: your question is referring to a 9 cell range but you are showing 10 values in the examples so that doesn't quite add up.......although I understand the problem.

The first part of your formula is OK because that's the standard way to count the number of different non-blank values......but when you subtract the second COUNTIF you also need to exclude blanks so you need to remove the &"" part in the second COUNTIF, i.e. this version

=SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10,A2:A10&"")-(COUNTIF(A2:A10,A2:A10)=1))

....but this version is better

=SUMPRODUCT((COUNTIF(A2:A10,A2:A10)>1)/COUNTIF(A2:A10,A2:A10&""))

Both of those formulas will work for either text or numeric data in A2:A10 (or a mixture of both) but for numeric values only (as per your example) you can also use FREQUENCY function like this

=SUMPRODUCT((FREQUENCY(A2:A10,A2:A10)>1)+0)

Improve this answer
1
  • This is absolutely perfect. You are correct on the 9 cell v 10 cell range; I didn't realize I had A2 in there, should have been A1. Thanks for the answers, this accommodates perfectly. I will use the frequency option for the one type of record, the other is mixed alpha numeric, so will use your simpler countif formula there. Bothe are perfect. Thanks again!
    – Andrew
    Oct 4, 2013 at 17:18

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .