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?

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

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?

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?

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?

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?

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?