How to form a =COUNTIF Column in reference to an already formulated 'Unique Column'

Tummi

To further explain in reference to this community post. here,

https://community.smartsheet.com/discussion/57396/generate-list-of-unique-values-index-distinct-vlookup/p1

I've successfully been able to apply the formulas suggested from that post and build out a column of unique names. Now in a separate column I'd like to be able to do a COUNTIF function in reference of the master sheet but output it on my new sheet with the unique column names field. Is it possible for me to do such? If so how?

Tummi

@Jeff Reisman

Thanks for the insight, I did try this and gave me the output of error #UNPARSEABLE.

You are correct my master sheet has a bunch of unique names with some of them repeating and on my helper sheet I was able to build out the formula -

=IFERROR(IF(ISNUMBER([Row ID]@row), INDEX({Master Sheet Names Column}, MATCH([Row ID]@row, {Master Sheet Helper Column}, 0))), "")

This provided me with the list of all the names from the master sheet. So attempting to do a COUNTIF statement to see how many times each of those names have shown up repeatedly it gave me the error of #UNPARSEABLE when using the function you told me to use.

Is there a possibility I could be doing something wrong? Or if the COUNTIF is not able to reference because of the complex formula to identify the unique names?

Jeff Reisman

@Tummi

Let me see if I understand what you're looking to do:

You have a helper sheet with a column of Unique Names.

You have a main sheet that has rows that include the Unique Names values, with those Unique Name values appearing on multiple rows.

Back on your helper sheet, you want to show a count of the total number of rows from the main sheet that include a given Unique Name, yes? For example, your helper sheet has Unique Names values such as Jeff123, Jared234, etc., and your main sheet may have 2 or more rows with Jeff123 as a value, or with Jared234 as a value, and you want the helper sheet to show how many rows there are for each unique name, right?

If so, you can do this easily with an COUNTIFS formula. Start by typing =COUNTIFS( and then Smartsheet will pop up a helper box. Click on "Reference Another Sheet," navigate to your main sheet, and select the header of the column containing the Unique name values. You can give the range a meaningful name if you want, otherwise the system will create a range reference for you like this:

{Main Sheet Name Range 1}

which represents the entire column of name values from your main sheet. Now your formula looks like this:

=COUNTIFS({Main Sheet Name Range 1}

The last step is to place a comma after the range, and then reference the Unique Names cell for the current row using the column name and @row:

=COUNTIFS({Main Sheet Name Range 1}, [Unique Names]@row)

In English, "Count the rows from the Main Sheet where the name is the same as the Unique Name value on this row."

If your result looks correct, right-click on your formula cell and scroll to the bottom of the context menu and select "Convert to column formula" to apply the formula instantly to the entire column.

Tummi

@Jeff Reisman

Thanks for the insight, I did try this and gave me the output of error #UNPARSEABLE.

You are correct my master sheet has a bunch of unique names with some of them repeating and on my helper sheet I was able to build out the formula -

=IFERROR(IF(ISNUMBER([Row ID]@row), INDEX({Master Sheet Names Column}, MATCH([Row ID]@row, {Master Sheet Helper Column}, 0))), "")

This provided me with the list of all the names from the master sheet. So attempting to do a COUNTIF statement to see how many times each of those names have shown up repeatedly it gave me the error of #UNPARSEABLE when using the function you told me to use.

Is there a possibility I could be doing something wrong? Or if the COUNTIF is not able to reference because of the complex formula to identify the unique names?

Jeff Reisman

@Tummi #UNPARSEABLE is one of the hardest errors to troubleshoot, because it's kind of a catch-all error:

#UNPARSEABLE

Cause

The formula has a problem which prevents it from being parsed and interpreted. This can happen for many reasons, such as misspelling, incomplete operators, using the wrong case for a column name, or using single quotes instead of double quotes.

Resolution

Ensure that all column names are spelled correctly in cell references, operators are being used correctly, and any text strings in the formula are surrounded by double quotes (" ").

Can you share a screenshot of what your data looks like, both in the master sheet and in the helper sheet? And also a screenshot and the text of the COUNTIFS formula you are using?