SUMPRODUCT and finding text within a cell
 

I wonder if someone can help me.

I am trying to count the number of times a particular word describing a
resource type occurs in an array, based on other criteria.

The basic SUMPRODUCT formula works fine, but only if the word is the only
word in the cell. I want to be able to count if the word occurs in the cell
at all.

The working formula is as follows:
=SUMPRODUCT(--(K15:K597210)*($G$15:$G$59721="Broker")*($I$15:$I $59721))

where K15:K59721 is a number of days, G15:G59721 is the array I wish to
search and I15:I59721 is the number of resources required.

What I want is to be able to find "Broker" if it occurs in any part of the
array, e.g. in a cell containing "Architect,Broker". However, when I wildcard
the word in the formula it returns 0.

Can anyone help?

SUMPRODUCT and finding text within a cell
 

On Tue, 7 Oct 2008 05:10:01 -0700, tigger
wrote:

I wonder if someone can help me.

I am trying to count the number of times a particular word describing a
resource type occurs in an array, based on other criteria.

The basic SUMPRODUCT formula works fine, but only if the word is the only
word in the cell. I want to be able to count if the word occurs in the cell
at all.

The working formula is as follows:
=SUMPRODUCT(--(K15:K597210)*($G$15:$G$59721="Broker")*($I$15:$I $59721))

where K15:K59721 is a number of days, G15:G59721 is the array I wish to
search and I15:I59721 is the number of resources required.

What I want is to be able to find "Broker" if it occurs in any part of the
array, e.g. in a cell containing "Architect,Broker". However, when I wildcard
the word in the formula it returns 0.

Can anyone help?

Try:

=SUMPRODUCT(--(K15:K597210)*NOT(ISERR(SEARCH("broker",$G$15:$G$ 59721)))*($I$15:$I$59721))

Note that the SEARCH worksheet function is case insensitive. If you need case
sensitivity, use FIND.
--ron

SUMPRODUCT and finding text within a cell
 

Hi,

Try this

=SUM(IF(ISNUMBER(SEARCH("Broker",G15:G59721)),IF(K 15:K597210,I15:I59721)))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
just bt pressing enter. If you do it correctly Excel will put curly brackets
around the formula {}. You can'nt type these yourself.

Mike

"tigger" wrote:

I wonder if someone can help me.

I am trying to count the number of times a particular word describing a
resource type occurs in an array, based on other criteria.

The basic SUMPRODUCT formula works fine, but only if the word is the only
word in the cell. I want to be able to count if the word occurs in the cell
at all.

The working formula is as follows:
=SUMPRODUCT(--(K15:K597210)*($G$15:$G$59721="Broker")*($I$15:$I $59721))

where K15:K59721 is a number of days, G15:G59721 is the array I wish to
search and I15:I59721 is the number of resources required.

What I want is to be able to find "Broker" if it occurs in any part of the
array, e.g. in a cell containing "Architect,Broker". However, when I wildcard
the word in the formula it returns 0.

Can anyone help?

SUMPRODUCT and finding text within a cell
 

Hi Mike,

This works great! Can I copy this formula to other cells by dragging or must
I use CTRL+SHIFT+ENTER each time?

Thanks!

"Mike H" wrote:

Hi,

Try this

=SUM(IF(ISNUMBER(SEARCH("Broker",G15:G59721)),IF(K 15:K597210,I15:I59721)))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
just bt pressing enter. If you do it correctly Excel will put curly brackets
around the formula {}. You can'nt type these yourself.

Mike

"tigger" wrote:

I wonder if someone can help me.

I am trying to count the number of times a particular word describing a
resource type occurs in an array, based on other criteria.

The basic SUMPRODUCT formula works fine, but only if the word is the only
word in the cell. I want to be able to count if the word occurs in the cell
at all.

The working formula is as follows:
=SUMPRODUCT(--(K15:K597210)*($G$15:$G$59721="Broker")*($I$15:$I $59721))

where K15:K59721 is a number of days, G15:G59721 is the array I wish to
search and I15:I59721 is the number of resources required.

What I want is to be able to find "Broker" if it occurs in any part of the
array, e.g. in a cell containing "Architect,Broker". However, when I wildcard
the word in the formula it returns 0.

Can anyone help?

SUMPRODUCT and finding text within a cell
 

Hi,

Once it's entered as an array then it can be dragged without having to
re-enter. Don't dismiss the non arrray sumproduct in your other response, I
only posted this as an alternative and generally the recieved wisdom is that
if can go non-array you should, It's less error prone.

Mike

"tigger" wrote:

Hi Mike,

This works great! Can I copy this formula to other cells by dragging or must
I use CTRL+SHIFT+ENTER each time?

Thanks!

"Mike H" wrote:

Hi,

Try this

=SUM(IF(ISNUMBER(SEARCH("Broker",G15:G59721)),IF(K 15:K597210,I15:I59721)))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
just bt pressing enter. If you do it correctly Excel will put curly brackets
around the formula {}. You can'nt type these yourself.

Mike

"tigger" wrote:

I wonder if someone can help me.

I am trying to count the number of times a particular word describing a
resource type occurs in an array, based on other criteria.

The basic SUMPRODUCT formula works fine, but only if the word is the only
word in the cell. I want to be able to count if the word occurs in the cell
at all.

The working formula is as follows:
=SUMPRODUCT(--(K15:K597210)*($G$15:$G$59721="Broker")*($I$15:$I $59721))

where K15:K59721 is a number of days, G15:G59721 is the array I wish to
search and I15:I59721 is the number of resources required.

What I want is to be able to find "Broker" if it occurs in any part of the
array, e.g. in a cell containing "Architect,Broker". However, when I wildcard
the word in the formula it returns 0.

Can anyone help?

SUMPRODUCT and finding text within a cell
 

Forgot to mention that if you want to drag it make the references absolute
with the $ sign

"Mike H" wrote:

Hi,

Once it's entered as an array then it can be dragged without having to
re-enter. Don't dismiss the non arrray sumproduct in your other response, I
only posted this as an alternative and generally the recieved wisdom is that
if can go non-array you should, It's less error prone.

Mike

"tigger" wrote:

Hi Mike,

This works great! Can I copy this formula to other cells by dragging or must
I use CTRL+SHIFT+ENTER each time?

Thanks!

"Mike H" wrote:

Hi,

Try this

=SUM(IF(ISNUMBER(SEARCH("Broker",G15:G59721)),IF(K 15:K597210,I15:I59721)))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
just bt pressing enter. If you do it correctly Excel will put curly brackets
around the formula {}. You can'nt type these yourself.

Mike

"tigger" wrote:

I wonder if someone can help me.

I am trying to count the number of times a particular word describing a
resource type occurs in an array, based on other criteria.

The basic SUMPRODUCT formula works fine, but only if the word is the only
word in the cell. I want to be able to count if the word occurs in the cell
at all.

The working formula is as follows:
=SUMPRODUCT(--(K15:K597210)*($G$15:$G$59721="Broker")*($I$15:$I $59721))

where K15:K59721 is a number of days, G15:G59721 is the array I wish to
search and I15:I59721 is the number of resources required.

What I want is to be able to find "Broker" if it occurs in any part of the
array, e.g. in a cell containing "Architect,Broker". However, when I wildcard
the word in the formula it returns 0.

Can anyone help?

SUMPRODUCT and finding text within a cell
 

I'm trying both to see which works best for the requirements.

Thanks to you both for help!

"Mike H" wrote:

Hi,

Once it's entered as an array then it can be dragged without having to
re-enter. Don't dismiss the non arrray sumproduct in your other response, I
only posted this as an alternative and generally the recieved wisdom is that
if can go non-array you should, It's less error prone.

Mike

"tigger" wrote:

Hi Mike,

This works great! Can I copy this formula to other cells by dragging or must
I use CTRL+SHIFT+ENTER each time?

Thanks!

"Mike H" wrote:

Hi,

Try this

=SUM(IF(ISNUMBER(SEARCH("Broker",G15:G59721)),IF(K 15:K597210,I15:I59721)))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
just bt pressing enter. If you do it correctly Excel will put curly brackets
around the formula {}. You can'nt type these yourself.

Mike

"tigger" wrote:

I wonder if someone can help me.

I am trying to count the number of times a particular word describing a
resource type occurs in an array, based on other criteria.

The basic SUMPRODUCT formula works fine, but only if the word is the only
word in the cell. I want to be able to count if the word occurs in the cell
at all.

The working formula is as follows:
=SUMPRODUCT(--(K15:K597210)*($G$15:$G$59721="Broker")*($I$15:$I $59721))

where K15:K59721 is a number of days, G15:G59721 is the array I wish to
search and I15:I59721 is the number of resources required.

What I want is to be able to find "Broker" if it occurs in any part of the
array, e.g. in a cell containing "Architect,Broker". However, when I wildcard
the word in the formula it returns 0.

Can anyone help?