COUNTIFS using only distinct/unique values

jordanlamagna

Hi!

I'm trying to count each row that 1) has the Parent Row checked, then 2) each of those that has a unique value in the Row ID column. (meaning if there's duplicate numbers, like rows 0062, I want it to only count once).

Not sure what that formula would look like.

Thank you!

Paul Newcome

You would use a COUNT/DISTINCT/COLLECT combo along the lines of

=COUNT(DISTINCT(COLLECT({Row ID}, {Checkbox}, @cell = 1)))

jordanlamagna

https://community.smartsheet.com/discussion/comment/472689#Comment_472689

I input it like this and it comes back with 1, which definitely isn't the case. I also directly copy and pasted what you sent and this also came back as 1.

Kelly Moore

Hey @jordanlamagna

I believe I see the issue.

The DISTINCT function will only work on a column that has values of the same data type (numeric, text, dates, etc). It appears from the leading zeros in your Row ID that you have a mixture of textstrings and numeric values.

To get around your mixture of textstrings and numeric values, you need a helper column (maybe two) to first find your unique values. A checkbox column will work. You can hide this column and/or push it to the far right of your sheet. If you do not already have the system column "Created (date)" or the system generated [Row ID] (this column is different than the one you named [Row ID] as the system will only give unique values), you will need to add one of these as well. One or the other of these system columns are required as it will give you a location placeholder as you travel down your sheet. Once you have the unique values identified, counting these rows becomes straight-forward.

For the sake of the formula, I'm going to used the [System Row ID] as the placeholder. You can substitute [Created] in it's place if you use that system column.

In the new checkbox helper column, insert this column formula

=IF(COUNTIFS([Row ID]:[Row ID], AND(@cell, [Row ID]@row)), [System Row ID]:[System Row ID], @cell <= [System Row ID]@row) = 1, 1)

The formula in your summary field is

COUNTIFS([New helper checkbox column]:[New helper checkbox column], 1)

*Be sure to correct the formula above with the exact name of your helper checkbox column

Does this work for you?
Kelly

jordanlamagna

https://community.smartsheet.com/discussion/comment/472775#Comment_472775

this says UNPARSEABLE when I input it

Kelly Moore

Hey @jordanlamagna

My bad. You can see immediately that the pink parenthesis for COUNTIFS is closed too early. Always follow the opening and closing pairs of parentheses by looking at colors.

Try this. The final parenthesis should always be blue.

=IF(COUNTIFS([Row ID]:[Row ID], AND(@cell, [Row ID]@row), [System Row ID]:[System Row ID], @cell <= [System Row ID]@row) = 1, 1)

Does this work for you?
Kelly

jordanlamagna

https://community.smartsheet.com/discussion/comment/472810#Comment_472810

Now it says INVALID DATA TYPE. :( The last Parentheses is blue. Is it something to do with the Request Created row?

Kelly Moore

Hey

This is very weird. The formula I did copy and I'm re-copying does not paste as it is written in my sheet.

This is what happens when I paste- which is the formula above:

=IF(COUNTIFS([Row ID]:[Row ID], AND(@cell, [Row ID]@row)), [System Row ID]:[System Row ID], @cell <= [System Row ID]@row) = 1, 1)

Here is the formula I wanted to share. I don't know when the 'copy' function stopped working correctly

Here it is manually corrected for your sheet. I apologize - I didn't realize I needed to correct a working formula after I pasted it in here

=IF(COUNTIFS([Row ID]:[Row ID], AND(@cell<>"", HAS([@cell, Row ID]@row)), [System Row ID]:[System Row ID], @cell <= [System Row ID]@row) = 1, 1)

Kelly

Paul Newcome

@Kelly Moore That square bracket is still misplaced. If you can still edit your post, it should be moved to before Row ID]@row instead of [@cell.

Kelly Moore

Thanks @Paul Newcome

This glitchy paste is driving me nuts

jordanlamagna

https://community.smartsheet.com/discussion/comment/473350#Comment_473350

Alright, we're getting closer lol. It's now checking the boxes. However, I only want it to check if the Parent Row column is also checked. Secondly, It's not checking columns that have duplicates at all, but I want to count all instances of a number as 1. So for example, here, the 0056 rows aren't checked because they are duplicates, but when I go to count this helper column, I want those two rows to count as 1. Please let me know if this makes any sense lol. Thank you so much for taking the time to help with this!

jordanlamagna

forgot to post the pic

Kelly Moore

@jordanlamagna

=IF(COUNTIFS([Row ID]:[Row ID], AND(@cell<>"", HAS([@cell, [Row ID]@row)), [System Row ID]:[System Row ID], @cell <= [System Row ID]@row, [Parent Row?]:[Parent Row?],1) = 2, 1)

This will check the first duplicate, with the Parent Row checked, that the formula encounters. Subsequent duplicates will not be checked since you are wanting only to count duplicates as 1 count for the set.

Does this work for you?
Kelly

jordanlamagna

https://community.smartsheet.com/discussion/comment/473460#Comment_473460

I got UNPARSABLE for this :(

Kelly Moore

@jordanlamagna

Please check the spelling of the column names. Note that the column names are not colored. Unparseable errors typically come from missing/misspellings in column names, misplaced/missing commas, mispaced/missing parentheses. If possible, add the column names by clicking on a field in that column so the reference is inserted by the sheet.

Paul Newcome

Looks like it could be that square bracket before @cell.