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!

(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, ""))

<tbody>
</tbody>

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

<tbody>
</tbody>

 

[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.