Count searched strings only once per cell

[ElmerS]

Hi,

Need to count Mother OR Father - but not both.

Father OR Mother were found 5 times in A1:A6

Pls see attached WB.

Thanks, Elm

[contaminated]

Hi. Try this one

=COUNT(SEARCH({"Mother*","Father*"},A1:A6))-COUNT(SEARCH({"Mother Father","Father Mother"},A1:A6))

[ElmerS]

Thank you, Vusal,

You understood what I'm looking for but it seems I was not clear enough.

The 5 cells were only a short and simple example.

There could be many combinations - such as:

Mother Son Father
Mother Daughter Father

or should I say "endless" combinations in each cell.

Elm

[zbor]

This?

=COUNTIF(A1:A10, "Mother") + COUNTIF(A1:A10, "Father")

[ElmerS]

Thanks but I don't think so - because "this" returns 2 instead of 7 - as shown in the attached pic.

Elm

[zbor]

Then this,..

=COUNTIF(A1:A10,"*"& "Mother"&"*") + COUNTIF(A1:A10, "*"&"Father"&"*")

[ElmerS]

If it only would return 7 (as per the last pic I have attached).

At my end it returns 11.

Elm

[pike]

Hi ElmerS
a poor second choice will be code

Please Login or Register  to view this content.

[pike]

or

Please Login or Register  to view this content.

[pike]

time for bed
this is set up as a udf

Please Login or Register  to view this content.

[ElmerS]

Three !!! separate thanks and good night.

Elm

[martindwilson]

try
=SUMPRODUCT(--(INDEX(--ISNUMBER(SEARCH("mother",A1:A10))&--ISNUMBER(SEARCH("father",A1:A10)),0)<>"00"))

[ElmerS]

This time a HUGE thank you.

Have a nice afternoon,

Elm