# Ignoring duplicates in formula

1. ## Ignoring duplicates in formula

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.

2. ## Re: Ignoring duplicates in formula

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

3. ## Re: Ignoring duplicates in formula

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

4. ## Re: Ignoring duplicates in formula

Originally Posted by Sambo kid
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.

5. ## Re: Ignoring duplicates in formula

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

6. ## Re: Ignoring duplicates in formula

maybe this way

7. ## Re: Ignoring duplicates in formula

Originally Posted by martindwilson
maybe this way
Worked like a charm, thanks!

8. ## Re: Ignoring duplicates in formula

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1