Count of duplicate values in a column returns #UNPARSEABLE

Trevor Knox

I'm trying to count the number of requirements that are the same in a column. My research tells me that I need to use COUNTIF, but it returns #UNPARSEABLE

This is the formula I'm using: =COUNTIF(({Requirement name}:{Requirement name}), ({Requirement name}@row))

Please help!

PS: I am also counting the number of unique values in the column and the formula works great: =COUNT(DISTINCT({Requirement name}))

KPH

Hi @Trevor Knox

=COUNTIF({Requirement name}, {Requirement name}@row)

Will not work because of the curly brackets around the Requirement name @row.

I am not sure you need a COUNTIF but I am going to explain it (you'll need it one day) and then suggest an alternative.

A COUNTIF function looks in a column. In your case this is a cross sheet reference to a column in another sheet that you have called Requirement name. All good so far.

It then matches against a value. In this case [Requirement name]@row

So you need to have a Requirement name column in the sheet with the formula. It needs this to match on.

If the data in the row matches the data in the Requirements column is it counted. It creates a grid, not a single number.

To illustrate what I mean, let's say, Requirements Sheet looks like this:

In your second sheet you can enter a formula by cross referencing the Requirement column and giving it the name {Requirements name}.

This you are doing with your COUNT DISTINCT formula:

 =COUNT(DISTINCT({Requirement name})) 

It returns the count of distinct items in the column. In my case 11.

That works well.

Now for a COUNTIF you need two parts. The range and the criterion. Your range can be your cross sheet reference, just like the COUNT DISTINCT, and then you need the criterion; the thing to check for. So I could do this:

=COUNTIF({Requirement name}, [Requirement to check for]@row)

This looks for and counts the number of rows where the data in the current sheet in the column Requirements to check for, is also in the Requirements column in the Requirements Sheet. This is what I thought you were doing, yesterday.

You'd have a list of Requirements and a COUNTIF for each one.

You can also use this formula to do things like count the number of completed requirements.

=COUNTIF({Requirements Sheet Completed}, 1)

The criterion either needs to be in the sheet with the formula like the 1st example, or be a fixed value, like the second example.

Now, onto what you want to achieve.

I don't think you want a list of requirements and the number of times they appear in the list. It sounds like you just want a total count of duplicated requirements.

Would a simple COUNT of the requirements minus DISTINCT do what you need?

This counts the number of rows in the Requirement name column.

=COUNT({Requirement name})

This gives you the total (in my case 13). You can then subtract the number of distinct requirements (11) to get the count of duplicates (2).

=COUNT({Requirement name}) - COUNT(DISTINCT({Requirement name}))

Does that help?