SUMPRODUCT but with text containing

[Guest]

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

 

[Guest]

One way is like this

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

 

[Guest]

Thanks for that. I"ll give that a goo.

What about if I wanted to use the Cell Reference,(ie A15) instead of the
actual text (makes it easier when applying the code to several rows.??

 

[Guest]

You could try

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

 

[Guest]

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

 

[Bob Phillips]

dl gave you

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


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

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

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