=COUNTIF

[Colt_Fisher_43]

Hello,

I have a working formula =COUNTIF(E2:H2,"<>Adams")

How would I add another name like "Davis" so it'll work with both names and remove duplicates?

Thank you

 

[Colt_Fisher_43]

Glad to help & thanks for the feedback.

Okay, I hope this is the last time I have a question, so I apologize in advance. If one of those columns is left blank how would I add a null or make sure it doesn't count a name if left empty in one of those columns?

=SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(E2&" "&F2&" "&G2&" "&H2,"Adams",""),"Davis",""))," ",", ")

 

[Fluff]

I'm afraid I have no idea what you are asking.

 

[Colt_Fisher_43]

I'm afraid I have no idea what you are asking.

I'll make a mock file and share it with you if you don't mind.

 

[Fluff]

That's fine.

 

[Colt_Fisher_43]

That's fine.

I attached a screen shot, I didn't see where I could upload an excel file. If I leave column "B" empty it's still counting it in the total in column "F" using this formula. I'm trying not to count a blank in column "F". I hope this helps

Formula in column "F" =COUNTIFS(A2:D2,"<>Adams",A2:D2,"<>Davis")

 

[Fluff]

How about

=COUNTIFS(A2:D2,"<>Adams",A2:D2,"<>Davis",A2:D2,"<>")

 

[Colt_Fisher_43]

How about

=COUNTIFS(A2:D2,"<>Adams",A2:D2,"<>Davis",A2:D2,"<>")

Yep, that was it! I was close. Thank you again.

 

[Fluff]

My pleasure.

 

[Colt_Fisher_43]

My pleasure.

Would I be able to remove duplicates with this formula? Let's say I don't want the name "Fisher" or any other names that I might enter in columns E2:H2 to show up twice if they are entered in more than once in those columns?

=SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(E2&" "&F2&" "&G2&" "&H2,"Davis",""),"Adams",""))," ",", ")

 

[Fluff]

No you couldn't substitute will remove all of them.