COUNTIF for a partial value

[Jonah]

What do I use to count partial cell contents?

I tried COUNTIF(S$2:S$61,"Y7-E4") where Y7-E4 is NOT a formula.

The cell being counted contains COUNTIF(S$2:S$61,"Y7-E4 XYZ")

I would like to be able to count occurences of E4

Jonah

 

[KL]

Hi Jonah,

Try this:

=COUNTIF(S$2:S$61,"*E4*")

or

=COUNTIF(S$2:S$61,"*Y7-E4*")

or even

=COUNTIF(S$2:S$61,"*Y7*-E4*")

etc.

See Help regarding wildcard characters

Regards,
KL

 

[Bob Phillips]

=SUMPRODUCT(--(LEFT(S$2:S$61,5)="Y7-E4"))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Jonah]

Brilliant answer. Thankyou so much.

Maybe a second answer?
In row 100 I have:-
=COUNTIF(B$2:B$61,"*Y11-D3*") =COUNTIF(C$2:C$61,"*Y11-D3*")

In row 101 I have:
=COUNTIF(B$2:B$61,"*Y11-D4*") =COUNTIF(C$2:C$61,"*Y11-D4*")

Why do I get a small triangle at the top left corner of each cell for
all items in row 100, notifying me of an error.

The items being searched and counted are both current on the
spreadsheet. I am puzzled.

Jonah

----------------------------------------
Reply from KL was:
Try this: =COUNTIF(S$2:S$61,"*E4*") or
=COUNTIF(S$2:S$61,"*Y7-E4*") or even
=COUNTIF(S$2:S$61,"*Y7*-E4*")

See Help regarding wildcard characters

 

[KL]

Looks OK to me. If the row is 100, then circular refernce is impossible. Not
a clue, I am afraid. Have you tried to click on the triangle to get further
info on the error?

Regards,
KL