4 Replies Latest reply on Sep 6, 2012 1:05 PM by Nick Zajchowski

# Summing Duplicate Values

I have a data set that has unique identifiers for each person (df_nr). Each person has a record every time they were sent to jail (bk_nr). I developed a calculation to determine the number of days spent in jail (using dates of entry/release) for each bk_nr. The problem is that there are duplicate records for each bk_nr, so I cannot sum up total number of days in jail by df_nr, because when it sums, it includes the days in jail for the duplicated bk_nrs.

I tried the ATTR function, but it only produces a correct figure for the bk_nr - it will not let me sum the jail days by df_nr.

Any thoughts?

• ###### 1. Re: Summing Duplicate Values

Likely a simple change to the formula or within the worksheet is needed; but instead of listing numerous things to try, would you be able to post a sample workbook so folks have a better idea of the data and how you've configured your chart/table? That way the response is tailored specifically to what you're experiencing.

• ###### 2. Re: Summing Duplicate Values

Andy - Sure - should have done this to begin with. I warn you that the data set is not pretty (this is a small piece of that set). The "Test" worksheet shows the sum problem I'm having. The second worksheet shows the real values for each booking number (which I got by taking the average). As I mentioned above, I'm trying to sum the jail days for each defendant.

Thanks, everyone for your help - I try to exhaust all avenues before posting here and you haven't let me down yet!

• ###### 3. Re: Summing Duplicate Values

Nick,

If all the records are identical, consider using a MIN or MAX instead of SUM. This would keep your formula, but only give a single value instead of a multiple of the number of records you have.

Andy

• ###### 4. Re: Summing Duplicate Values

Thanks, Andy, that works for viewing the values by booking number, but does not seem to work if I want to sum all of the jail days for each defendant.