Checking for duplicates by matching 3 separate criteria

[iansberg]

I have a column (A) of dates from 2007-2014 (m/d/yr). I have another column (B) of analytes that are tested. My last column (C) is a grouping of three categories, lets say categories 1,2,3. So for each date a set of analytes are tested amongst 3 categories. I cant figure out a formula that looks at a specific analyte name (str) to see if it occurs more than once in a given date for each category.

The question really is "Does my analyte of the category (1,2 or 3) appear more than once on the day it was tested(column A range).

Make sense???
.

Date Parameter Group
11/1/2011 LAGOON_1_LEVEL 1
11/1/2011 LAGOON_2_LEVEL 1
11/1/2011 LAGOON_3_LEVEL 1
11/1/2011 FLOW_METER_NUM 1
11/1/2011 FOG_LAB 1
11/1/2011 BOD_LAB_MG_L 1
11/1/2011 BOD_HMI_LB_READING 1
11/1/2011 TOC_LAB 1
11/1/2011 TOC_METER_READ 1
11/1/2011 Total_Suspended_Solids 1
11/1/2011 pH_HIGH 1
11/1/2011 pH_LOW 1_Meter_Calc 1



Thanks!

 

[RudiS]

Try a countifs formula in column D

Something like this: =COUNTIFS($A$2:$A$13,A2,$B$2:$B$13,B2,$C$2:$C$13,C2)

If the same date, analyte and group is encountered more than once, the value of the formula is > 1 (basically the count of how many times it is duplicated)

If you don't want the name of the analyte checked, then just remove it from the formula.
IOW, it will only check date and group: =COUNTIFS($A$2:$A$13,A2,$C$2:$C$13,C2)