counting row values with multiple criteria and excluding duplicates

RaffyM

Hello. I am new here. Appreciate if some one could help me. I would like to count only row values excluding duplicate but meeting multiple criteria. Below is the sample of my smartsheet.

Is there an COUNTIF formula that will count unique values only once of Ref No column if Part Number and Order No is the same.

Thanks!

David Tutwiler

Could you not just count the Distinct number of Reference Numbers? Something like:

=COUNT(DISTINCT([Ref No]:[Ref No]))

Outside of that, I think you'd have to have a helper checkbox where you checked to see if a line was a duplicate and then subtracted that count from your total count.

David Tutwiler

Sorry, I'm a little confused at what you're looking for. Do you want to count the number of times that the reference numbers, part numbers, and order numbers match from column to column? Or are you looking to do a count (or sum up the quantity) when the part number and order number match, but the reference number doesn't match? I'm just confused on what it is you're looking to do.

When you reply, please @ mention me so that I see the reply and can get back to this more quickly.

RaffyM

@David Tutwiler thank you David for the response and sorry for the confusion.

What I would like to get is the total count of reference number excluding the duplicate values. In my example, 21-042 and 21-045 were entered twice because part number and order were matched. My desired count should be 9 only, not 11.

Thanks.

David Tutwiler

Could you not just count the Distinct number of Reference Numbers? Something like:

=COUNT(DISTINCT([Ref No]:[Ref No]))

Outside of that, I think you'd have to have a helper checkbox where you checked to see if a line was a duplicate and then subtracted that count from your total count.

RaffyM

Thank you @David Tutwiler, that would work. I should thought of that Distinct command, 😀

By the way, actually there is another issue I would like to get resolve from the same smartsheet. I am adding rows every now and then as a new data and it could be 10 to 20 rows each time. Instead of checking one by one if new part number added was present already with same order number, is there a way that will check automatically and will copy (or duplicate) reference number automatically on the new row added if part number and order number are exactly match from previous one? There is instance that part number is same but different order numbers.

Appreciate your help. Thanks again.

David Tutwiler

When I was thinking about your problem I actually made this formula to create a "Duplicate" column that would automatically check if there was a matching row in the sheet. You might be able to modify this formula to fit what you're trying to do, or you could add a Checkbox column called Duplicate and paste this code in that column.

=IF(COUNTIFS([Ref No]:[Ref No], [Ref No]@row, [Part Number]:[Part Number], [Part Number]@row, [Order No]:[Order No], [Order No]@row) > 1, 1, 0)

RaffyM

@David Tutwiler, I've tried formula similar to one you gave above and it help identify duplicate values. Thanks anyway.

What I'm trying to achieve is to have formula that will automatically copy the previous reference number if part number and order number on the new row/s added were exactly matched in previous row/s. For example, I added row 72. I can see that part number and order number are exactly the same of row 69. Instead of giving unique reference number to row 72, I want it to be the same of row 69. And formula (if possible) would do that for me, instead of adding manually.

Thank you.

David Tutwiler

I think your best bet for something like this is to access the API, but I can't think of a formula that would do this automatically.