I've noticed that it won't count the number of cells that contain just the
words 'Newspaper Ad'. I think this is because the char number is different.

=SUMPRODUCT(--(prospects!$C$2:$C$64=B$10),--(LEFT(prospects!$BL$2:$BL$64,26)=$A11))

The problem is I have several 'criteria' (on each row of the table) to match
that all vary in character lengths ie from 8 - 26 characters + what may be
after those (the text that can change).

If you could suggest what I can do, I would appreciate it.

Cheers
Fiona

"daddylonglegs" wrote:

One way is like this

=SUMPRODUCT(--(prospects!$C$2:$C$64=B$10),--(LEFT(prospects!$BL$2:$BL$64,12)="Newspaper Ad"))

"Fiona" wrote:

I am trying to count the number of entries that appear in a range of data and
see if it matches a cell (or text) that I have. The problem is the text
could contain a variety of data after the beginning of the statment.

Ie Newspaper Ad could be "Newspaper Ad: The Age", or "Newspaper Ad: Courier
Mail" etc.
I've tried using a wildcard * but it doesn't seem to work.

=SUMPRODUCT(--(prospects!$C$2:$C$64=B$10),--(prospects!$BL$2:$BL$64="Newspaper Ad*"))

Could you please let me know what I should be doing?

Cheers