COUNTIF Formula

Sidonia Posteuca

Hello!

I am trying to set up a formula in my sheet summary that counts a cell if it includes one of three names. So far I have tried the following but am either getting 0 or an error message..

=COUNTIFS(OR(FIND("Name1", [ASSIGNED PREFAB POC]:[ASSIGNED PREFAB POC]), FIND("Name2", [ASSIGNED PREFAB POC]:[ASSIGNED PREFAB POC]), FIND("Name3", [ASSIGNED PREFAB POC]:[ASSIGNED PREFAB POC])))

=COUNTIF([ASSIGNED PREFAB POC]:[ASSIGNED PREFAB POC], "Name1") + COUNTIF([ASSIGNED PREFAB POC]:[ASSIGNED PREFAB POC], "Name2") + COUNTIF([ASSIGNED PREFAB POC]:[ASSIGNED PREFAB POC], "Name3")

I do not want to count any cells more than once.

Please help!

Zachary Hall

That is why... This should help:

• COUNTIF([ASSIGNED PREFAB POC]:[ASSIGNED PREFAB POC], FIND("Name1",@cell)>0)+COUNTIF([ASSIGNED PREFAB POC]:[ASSIGNED PREFAB POC], FIND("Name2",@cell)>0)+COUNTIF([ASSIGNED PREFAB POC]:[ASSIGNED PREFAB POC], FIND("Name3",@cell)>0)

Your names will have to appear exactly as they do in the contact column (which could also be their email address).

I hope this helps!

Zachary Hall

Do the cells that contain the names have multiple names in them? If so, are they contact cells or just normal text in a string format?

Sidonia Posteuca

Yes. It is a contact list column. Some cells have one name, some have two, some have three.

Zachary Hall

That is why... This should help:

• COUNTIF([ASSIGNED PREFAB POC]:[ASSIGNED PREFAB POC], FIND("Name1",@cell)>0)+COUNTIF([ASSIGNED PREFAB POC]:[ASSIGNED PREFAB POC], FIND("Name2",@cell)>0)+COUNTIF([ASSIGNED PREFAB POC]:[ASSIGNED PREFAB POC], FIND("Name3",@cell)>0)

Your names will have to appear exactly as they do in the contact column (which could also be their email address).

I hope this helps!

Sidonia Posteuca

It worked. Thank you!

Will this @cell in the formula mess up any cell links in the sheet?

Zachary Hall

Nope! You should be good to go :)