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?

KPH

Hi @Trevor Knox

The issue is that you do not have square brackets around your column name. If your column name contains spaces you must use [ and ]. Like this

=IF(COUNT([Requirement name]@row) =COUNTIFS([Requirement name]:[Requirement name], [Requirement name]@row), 1)

KPH

It looks like you are mixing your your references.

Curly brackets are used for cross sheet references and an entire column would be referenced like

{Requirement name}

Square brackets are used for within sheet column references and an entire column would be referenced like

[Requirement name]:[Requirement name]

or a single cell on the current row would be reference like

[Requirement name]@row

This will be very confusing and won't work

• {Requirement name}:{Requirement name}
• {Requirement name}@row

You also have a bunch of extra parentheses.

Try using this in the sheet that contains the Requirements name column.

=COUNTIF([Requirement name]:[Requirement name], [Requirement name]@row)

Trevor Knox

Thanks for your answer! I'm calculating these on a separate sheet being used to generate metrics for a dashboard, so it seems I need to use the curly brackets since I'm not working within the sheet with the data. I'm wanting to calculate the number of duplicates within the Requirement name column in another sheet.

I changed the formula to =COUNTIF({Requirement name}, {Requirement name}@row) but this still doesn't work. Any further ideas?

As an aside, in another cell I'm counting the number of unique requirements with =COUNT(DISTINCT({Requirement name})) and that works well. Is there an option to do the opposite of the DISTINCT function?

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?

heyjay

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

Just fixing your initial formula.

Trevor Knox

@KPH aha! Thanks for walking me through all of that. I realize now what I was initially doing wasn't right because the content I'm comparing to isn't actually in this metric sheet. Thinking about it the way you have with counting the total requirements, and subtracting the distinct ones (which I've already calculated) is so much simpler.

Thanks so much for your help! That worked perfectly and equals the difference between my total requirements and the distinct ones.

KPH

Yes, that is exactly what the problem was. Now you have a simple way to do what you want and you know how to use COUNTIFS in the future. Great news! Happy I could help.

Trevor Knox

@KPH me again :). I'm not trying to create a report that will show only unique requirements. I believe to do this I need to add a column to my sheet with requirements that will return either a 0 or a 1 based on a COUNTIF, and then filter by that value in the report I want only unique requirements.

Could you walk me through making my =COUNT(DISTINCT({Requirement name})) formula into a COUNTIF that will give me that check?

I tried =COUNTIF(DISTINCT({Requirement name}), 1) but I don't think its quite right. Thanks in advance!

KPH

Hi again @Trevor Knox

You need to add your column (checkbox type) to the sheet with the requirements in, you can't use a cross reference for this.

To check a box for unique values only

You can use this formula to check the box next to each unique requirement:

=IF(COUNT(Requirement@row) = COUNTIFS(Requirement:Requirement, Requirement@row), 1)

The output will be something like this:

Which is lovely. But I am not sure it is what you want. 😕 Any duplicated requirements are not ticked and therefore won't be in your report.

If this is what you want, that is grand! Off you go.

However, if what you want is a list of all requirements but excluding duplicates (so in my case all the numbers 1-8) then you need a different formula. I'll add that here as well just in case. If you don't need it today, you might need it later.

To check a box for the first instance of a value (do not check duplicated entries)

You can use this formula to check the box next to the first instance of each requirement (so any duplicates are not checked):

=IF(COUNTIFS(Requirement$1:Requirement@row, Requirement@row) = 1, 1, 0)

The output would look like this:

Hope this helps.

Trevor Knox

Thanks again @KPH. The first option you provided is exactly what I'm looking to do. Unfortunately, after I've changed to column name to what I'm using, it returns as #Unparseable. I can't see whats wrong here. Can you have a look?

Here is the updated formula from you that I'm using:

=IF(COUNT(Requirement name@row) =COUNTIFS(Requirement name:Requirement name, Requirement name@row), 1)

Is the issue perhaps that I'm using 2 formulas in 1 cell? I wasn't aware you could do that, but perhaps thats my own ignorance! Thanks again for your help

KPH

Hi @Trevor Knox

The issue is that you do not have square brackets around your column name. If your column name contains spaces you must use [ and ]. Like this

=IF(COUNT([Requirement name]@row) =COUNTIFS([Requirement name]:[Requirement name], [Requirement name]@row), 1)

Trevor Knox

Amazing. That did the trick. Thanks for teaching me another Smartsheet quirk! I was wondering if quotes were required, so great to know square brackets are required. I am in your debt again!