Ignoring duplicates in formula

[xlsnovice]

Hi everyone, I am trying to get a value based on different values.

No Run	Office	QUALITY-CONTROL-EMPLOYEE
No Run	Office	QUALITY-CONTROL-EMPLOYEE
No Run	Office	QUALITY-CONTROL-EMPLOYEE
Passed	Office	ANALYST
No Run	Office	SECRETARY
No Run	Office	SECRETARY
No Run	Office	CEO
Failed	Office	OFFICE LEAD
Not Completed	Office	CEO

I need a formula that will give me a total for the "No Run", a total for "Passed", a total for "Failed" and a total for "Not Completed. Now comes the challenging part. The formula needs to check if the criteria "Office" in the second column is met, if it is met it will have to give me the total for "No Run" for example but only for unique values in column 3. So it should only give me a total of 3 for "No Run" because "No Run" only applies to three unique names in column 3.

[Sam Capricci]

Have you tried an If(and( ... stmt?

[Special-K]

Maybe use SUMPRODUCT with --, coercing to a count rather than a sum
or maybe pivot table?

[xlsnovice]

Have you tried an If(and( ... stmt?

Yes, I've tried an IF(AND(... statement but that didn't help me eliminate the duplicates. I've also tried a two part approach by first using a COUNTIF statement. The COUNT IF looks like this: COUNTIFS(A:A,"No Run",C:C,C1) That will show me how many times "No Run" in column A appears for the value in column C. I then use a DCOUNTA statement but that doesn't give me the correct amount.

[FDibbins]

Try this. Assuming your data is in A1C9 (your sample data), in D1, copied down, use this...
=IF(AND(B1="Office",COUNTIF($C$1:C1,C1)=1),A1,"")
or
=IF(COUNTIFS($B$1:B1,"Office",$C$1:C1,C1)=1,A1,"")

You can then base whatever counts you want, on that column

[martindwilson]

maybe this way

[xlsnovice]

maybe this way

Worked like a charm, thanks!

[mlcb]

assuming your data in A2:C10 maybe...

=SUMPRODUCT(--(A2:A10="No Run"),--(B2:B10="Office"),--(MATCH(C2:C10,C2:C10,0)=ROW(C2:C10)-ROW(C2)+1))

M.