Remove duplicate values from countifs formula

[lwthomp80]

I have a very involved formula that counts data based on about 5 different criteria; however, the criteria is not defined by the name column. This is where the problem comes in. When I get the count of data that fulfills the criteria, there are a few anomaly items where the same person, in the "Name" column, meets the criteria twice, so I am having to go through and do a sort based on the criteria and manually add "-1, -2, etc" to the end of the formulas that contain duplicate names.

Is there a way to identify the duplicate names after the criteria has been met, so that the count will not include names that appear more than once? Below if the formula I have so far....Thanks!

=(COUNTIFS(Type, "Away from Work", LT_Start,">="&DATE(2013,1,1),LT_Start,"<="&DATE(2014,1,31),LT_End,">="&DATE(2014,1,1), LT_End, "<="&DATE(2014,12,31))+COUNTIFS(Type, "Away from Work", LT_Start,">="&DATE(2013,1,1),LT_Start,"<="&DATE(2014,1,31),LT_End, ""))

 

[GerryZ]

Hello!
I like this question but I don't understand all of the issue It was nice to see a spreadsheet sample instead to use 1000s of words and formula
Can you post a screenshot with just few rows and colunms with the expected outcome so we can understand better?

help us to help you
Thank you in advance!

 

[lwthomp80]

How do I attach a version of the spreadsheet? Sorry....I am not familiar with posting to the forums.

 

[Aladin Akyurek]

@lwthomp80

The formula does not refer to a name range, does it? Are you trying to do a unique count of names under the conditions the formula specifies?

 

[lwthomp80]

This is a summarized version of my data that meets the criteria in the formula. The count that would be returned for the data below is 21; however, there are 4 duplicates (highlighted in red). I would have to go into the formula and add "-4" at the end of it to account for, and remove the 4 duplicates from the final count. Is there a way to remove them without having to highlight and manually add the subtraction? I hope this makes sense. Thanks in advance!

[TABLE="width: 728"]
<tbody>[TR]
[TD="class: xl67, width: 728"](COUNTIFS(LT_START,">="&DATE(2013,1,1),LT_START,"<="&DATE(2014,1,31),LT_End,">="&DATE(2014,1,1), LT_End, "<="&DATE(2014,12,31))+COUNTIFS(LT_START,">="&DATE(2013,1,1),LT_START,"<="&DATE(2014,1,31),LT_End, ""))[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD]NAME[/TD]
[TD]LT_START[/TD]
[TD]LT_End[/TD]
[/TR]
[TR]
[TD]BJRNEMM, JSJN[/TD]
[TD="align: right"] 8/31/2013[/TD]
[TD="align: right"] 6/10/2014[/TD]
[/TR]
[TR]
[TD]BJSINO, KODI[/TD]
[TD="align: right"] 1/26/2014[/TD]
[TD="align: right"] 6/2/2014[/TD]
[/TR]
[TR]
[TD]BJSINO, KODI[/TD]
[TD="align: right"] 1/23/2014[/TD]
[TD="align: right"] 1/25/2014[/TD]
[/TR]
[TR]
[TD]BOOMHE, JJMES[/TD]
[TD="align: right"] 1/8/2014[/TD]
[TD="align: right"] 1/12/2014[/TD]
[/TR]
[TR]
[TD]BOWERS, DJVID[/TD]
[TD="align: right"] 1/11/2014[/TD]
[TD="align: right"] 1/12/2014[/TD]
[/TR]
[TR]
[TD]BOYENGER, EDWJRD[/TD]
[TD="align: right"] 1/18/2014[/TD]
[TD="align: right"] 5/9/2014[/TD]
[/TR]
[TR]
[TD]BOYENGER, EDWJRD[/TD]
[TD="align: right"] 1/11/2014[/TD]
[TD="align: right"] 1/17/2014[/TD]
[/TR]
[TR]
[TD]BROOKS, CHRISMOPHER[/TD]
[TD="align: right"] 1/10/2014[/TD]
[TD="align: right"] 1/12/2014[/TD]
[/TR]
[TR]
[TD]BROWN, BJRRY[/TD]
[TD="align: right"] 11/27/2013[/TD]
[TD="align: right"] 3/3/2014[/TD]
[/TR]
[TR]
[TD]BRYJNM, MICHJES[/TD]
[TD="align: right"] 12/13/2013[/TD]
[TD="align: right"] 8/14/2014[/TD]
[/TR]
[TR]
[TD]CJRRIJGJ, JSEXJNDER[/TD]
[TD="align: right"] 1/30/2014[/TD]
[TD="align: right"] 2/1/2014[/TD]
[/TR]
[TR]
[TD]CONKSIN, JEFF[/TD]
[TD="align: right"] 10/24/2013[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DEJNE, SJURES[/TD]
[TD="align: right"] 12/31/2013[/TD]
[TD="align: right"] 1/2/2014[/TD]
[/TR]
[TR]
[TD]DJVIS, KEIMH[/TD]
[TD="align: right"] 12/21/2013[/TD]
[TD="align: right"] 2/10/2014[/TD]
[/TR]
[TR]
[TD]DJVJSOS, JOSEPH[/TD]
[TD="align: right"] 2/1/2013[/TD]
[TD="align: right"] 8/27/2014[/TD]
[/TR]
[TR]
[TD]DIG, WJYNE[/TD]
[TD="align: right"] 1/31/2014[/TD]
[TD="align: right"] 4/15/2014[/TD]
[/TR]
[TR]
[TD]DIG, WJYNE[/TD]
[TD="align: right"] 1/28/2014[/TD]
[TD="align: right"] 1/30/2014[/TD]
[/TR]
[TR]
[TD]EZESS, MHOMJS[/TD]
[TD="align: right"] 1/10/2014[/TD]
[TD="align: right"] 1/12/2014[/TD]
[/TR]
[TR]
[TD]FERNJNDEZ, JUJN[/TD]
[TD="align: right"] 5/1/2013[/TD]
[TD="align: right"] 1/3/2014[/TD]
[/TR]
[TR]
[TD]FIGUEROJ, WISFREDO[/TD]
[TD="align: right"] 1/3/2014[/TD]
[TD="align: right"] 3/6/2014[/TD]
[/TR]
[TR]
[TD]FIGUEROJ, WISFREDO[/TD]
[TD="align: right"] 12/27/2013[/TD]
[TD="align: right"] 1/2/2014[/TD]
[/TR]
</tbody>[/TABLE]

 

[lwthomp80]

No, the formula does not refer to a "Name" range...this is why I am running into the problems. This selected data is part of a much larger group of data that gets filtered and counted via the formula, and I am at a loss as to how to identify the names and remove them.

 

[gaz_chops]

This is probably not the best solution, but it appears to work if there are only ever 2 occurrences of the name

Array formula enter with Ctrl, Shift & Enter
=COUNTA(A2:A50)-SUM(COUNTA(A2:A50)-(SUM(COUNTIF(A2:A50,A2:A50))-COUNTA(A2:A50))/2)

 

[lwthomp80]

Thank you! But there are occurrences where the name appears three and four times.

 

[gaz_chops]

Sorry, you said "meets the criteria twice", so i thought only ever duplicates.

 

[lwthomp80]

Also, the data that I presented is part of a much larger group....I only showed the values that met the criteria of the formula in my example.