SUMPRODUCT but with text containing
 

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

SUMPRODUCT but with text containing
 

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

SUMPRODUCT but with text containing
 

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.??

"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

SUMPRODUCT but with text containing
 

You could try

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

"Fiona" wrote:

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.??

"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

SUMPRODUCT but with text containing
 

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

SUMPRODUCT but with text containing
 

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)

"Fiona" wrote in message
...
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="Newspap
er Ad*"))

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

Cheers