I would like to know if there is a way in excel to sumif based on if the
data contains particular words. For example; column A has data like (blue
circle, red circle, yellow circle, blue square, red square and so on.)
Column B has how many of each sold. I want to sum all the rows in column A
that contain a particular word (Circle, Square, Red, or Blue). I hope this
makes sense and any help would be great.

Thanks, Kevin

If I understood what you want:

=SUMPRODUCT(--(MMULT(--(ISNUMBER(SEARCH({"red","blue","circle","square"}, A2:A9))),{1;1;1;1})0),B2:B9)

--
Biff
Microsoft Excel MVP


"Kev30" wrote in message
...
I would like to know if there is a way in excel to sumif based on if the
data contains particular words. For example; column A has data like (blue
circle, red circle, yellow circle, blue square, red square and so on.)
Column B has how many of each sold. I want to sum all the rows in column
A
that contain a particular word (Circle, Square, Red, or Blue). I hope
this
makes sense and any help would be great.

Thanks, Kevin

Biff...Simply brilliant. Thanks for your super fast response. Thank you so
much for your help. I needed it to reference a cell but was able to make the
quick change.
=SUMPRODUCT(--(MMULT(--(ISNUMBER(SEARCH(D3,A$2:A$5462))),{1})0),B$2:B$54 62)

I did find one slight issue.
=SUMPRODUCT(--(MMULT(--(ISNUMBER(SEARCH(D3,A$2:A$5462))),{1})0),B$2:B$54 62)
works fine, but
=SUMPRODUCT(--(MMULT(--(ISNUMBER(SEARCH(D3,A$2:A$5463))),{1})0),B$2:B$54 63)
does not. Does it limit the lookup to 5462 rows or did I mess the whole
thing up by removing something?

If its not too much to ask I would love to know what all this is doing. In
particular the (--MMULT(--(ISNUMBER part of the formula. I have never come
across this. Can you enlighten me or point me somewhere to enhance my
knowledge of this formula.

Thanks again Biff,
Kevin


"T. Valko" wrote:

If I understood what you want:

=SUMPRODUCT(--(MMULT(--(ISNUMBER(SEARCH({"red","blue","circle","square"}, A2:A9))),{1;1;1;1})0),B2:B9)

--
Biff
Microsoft Excel MVP


"Kev30" wrote in message
...
I would like to know if there is a way in excel to sumif based on if the
data contains particular words. For example; column A has data like (blue
circle, red circle, yellow circle, blue square, red square and so on.)
Column B has how many of each sold. I want to sum all the rows in column
A
that contain a particular word (Circle, Square, Red, or Blue). I hope
this
makes sense and any help would be great.

Thanks, Kevin

The formula I suggested was based on your post and my understanding that you
seemed to want a sum if cells contained one of several words and the samples
you posted contained more than one of those words.

If you're only searching for a single word:

I needed it to reference a cell

Then we can simplify things greatly:

=SUMPRODUCT(--(ISNUMBER(SEARCH(D3,A$2:A$5462))),B$2:B$5462)

Or, even:

=SUMIF(A2:5462,"*"&D3&"*",B2:B5462)

Does it limit the lookup to 5462 rows

The MMULT function is limited to 5461 rows. The version above is not limited
except that you can't use entire columns as range references unless you're
using Excel 2007.

MMULT = matrix multiplication

I would love to know what all this is doing. In
particular the (--MMULT(--(ISNUMBER part of the formula.

It's kind of hard to explain but here's an explanation I wrote a while back
for someone else. The logic is the same but this example was for a slightly
different request.

==========

For an explanation lets use a very small sample:

......A.....B.....C
1...1......5.....10

You want to count how many times 5 and 10 appear on the same row.

A10 = 5
B10 = 10

=SUMPRODUCT(--(MMULT(--(ISNUMBER(MATCH(A1:C1,A10:B10,0))),{1;1;1})=2))

This portion of the formula tests to see if any numbers in A1:C1 match the
numbers in A10:B10

ISNUMBER(MATCH(A1:C1,A10:B10,0))

This will return a horizontal array of either TRUE or FALSE

FALSE TRUE TRUE

We need to convert those logical values to numbers. To do that we use the
double unary:

--(ISNUMBER(MATCH(A1:C1,A10:B10,0))

That will convert TRUE to 1 and FALSE to 0:

0 1 1

MMULT (matrix multiplication) is then used to return the count of matches
per row. We multiply the horizontal array

0 1 1 by a vertical array equal to the number of columns in the data set.
In this case we have 3 columns A1:C1, so the vertical array is {1;1;1}

It would look something like this:

0..1..1.......1
..................1
..................1

The 0 times the top vertical 1
The middle horizontal 1 times the middle vertical 1
The rightmost horizontal 1 times the bottom vertical 1

The result would be:

0*1 + 1*1 + 1*1
0 + 1 + 1 = 2

Now, imagine your sample with 3 rows of data. The MMULT function would
return a count like that above for each row. Those counts are then passed to
the SUMPRODUCT function where they are tested to see if they equal 2 (for 2
matches: A1:C1 matches A10 and A1:C1 matches B10).

This is also an array of TRUE or FALSE. Then we once again convert those
logical values to 1 and 0 then the SUMPRODUCT totals those to arrive at the
final result of 1 (based on this explanation sample).

==========


--
Biff
Microsoft Excel MVP


"Kev30" wrote in message
...
Biff...Simply brilliant. Thanks for your super fast response. Thank you
so
much for your help. I needed it to reference a cell but was able to make
the
quick change.
=SUMPRODUCT(--(MMULT(--(ISNUMBER(SEARCH(D3,A$2:A$5462))),{1})0),B$2:B$54 62)

I did find one slight issue.
=SUMPRODUCT(--(MMULT(--(ISNUMBER(SEARCH(D3,A$2:A$5462))),{1})0),B$2:B$54 62)
works fine, but
=SUMPRODUCT(--(MMULT(--(ISNUMBER(SEARCH(D3,A$2:A$5463))),{1})0),B$2:B$54 63)
does not. Does it limit the lookup to 5462 rows or did I mess the whole
thing up by removing something?

If it's not too much to ask I would love to know what all this is doing.
In
particular the (--MMULT(--(ISNUMBER part of the formula. I have never
come
across this. Can you enlighten me or point me somewhere to enhance my
knowledge of this formula.

Thanks again Biff,
Kevin


"T. Valko" wrote:

If I understood what you want:

=SUMPRODUCT(--(MMULT(--(ISNUMBER(SEARCH({"red","blue","circle","square"}, A2:A9))),{1;1;1;1})0),B2:B9)

--
Biff
Microsoft Excel MVP


"Kev30" wrote in message
...
I would like to know if there is a way in excel to sumif based on if
the
data contains particular words. For example; column A has data like
(blue
circle, red circle, yellow circle, blue square, red square and so on.)
Column B has how many of each sold. I want to sum all the rows in
column
A
that contain a particular word (Circle, Square, Red, or Blue). I hope
this
makes sense and any help would be great.

Thanks, Kevin

This works even better, thank you. Sorry for the slight mix up about what I
was looking for. I don't always give the best descriptions. I will have a
need for looking up more than one word in the future so that will be very
helpful.

I have one last question if it's not to much to ask. What does the -- do
in =SUMPRODUCT(--(ISNUMBER(SEARCH(D3,A$2:A$5462))),B$2:B$5462)?

Thanks for all you help. I love to know that there is a place out there
that I can seek out answers for the questions I have.

Kevin

"T. Valko" wrote:

The formula I suggested was based on your post and my understanding that you
seemed to want a sum if cells contained one of several words and the samples
you posted contained more than one of those words.

If you're only searching for a single word:

I needed it to reference a cell

Then we can simplify things greatly:

=SUMPRODUCT(--(ISNUMBER(SEARCH(D3,A$2:A$5462))),B$2:B$5462)

Or, even:

=SUMIF(A2:5462,"*"&D3&"*",B2:B5462)

Does it limit the lookup to 5462 rows

The MMULT function is limited to 5461 rows. The version above is not limited
except that you can't use entire columns as range references unless you're
using Excel 2007.

MMULT = matrix multiplication

I would love to know what all this is doing. In
particular the (--MMULT(--(ISNUMBER part of the formula.

It's kind of hard to explain but here's an explanation I wrote a while back
for someone else. The logic is the same but this example was for a slightly
different request.

==========

For an explanation lets use a very small sample:

......A.....B.....C
1...1......5.....10

You want to count how many times 5 and 10 appear on the same row.

A10 = 5
B10 = 10

=SUMPRODUCT(--(MMULT(--(ISNUMBER(MATCH(A1:C1,A10:B10,0))),{1;1;1})=2))

This portion of the formula tests to see if any numbers in A1:C1 match the
numbers in A10:B10

ISNUMBER(MATCH(A1:C1,A10:B10,0))

This will return a horizontal array of either TRUE or FALSE

FALSE TRUE TRUE

We need to convert those logical values to numbers. To do that we use the
double unary:

--(ISNUMBER(MATCH(A1:C1,A10:B10,0))

That will convert TRUE to 1 and FALSE to 0:

0 1 1

MMULT (matrix multiplication) is then used to return the count of matches
per row. We multiply the horizontal array

0 1 1 by a vertical array equal to the number of columns in the data set.
In this case we have 3 columns A1:C1, so the vertical array is {1;1;1}

It would look something like this:

0..1..1.......1
..................1
..................1

The 0 times the top vertical 1
The middle horizontal 1 times the middle vertical 1
The rightmost horizontal 1 times the bottom vertical 1

The result would be:

0*1 + 1*1 + 1*1
0 + 1 + 1 = 2

Now, imagine your sample with 3 rows of data. The MMULT function would
return a count like that above for each row. Those counts are then passed to
the SUMPRODUCT function where they are tested to see if they equal 2 (for 2
matches: A1:C1 matches A10 and A1:C1 matches B10).

This is also an array of TRUE or FALSE. Then we once again convert those
logical values to 1 and 0 then the SUMPRODUCT totals those to arrive at the
final result of 1 (based on this explanation sample).

==========


--
Biff
Microsoft Excel MVP


"Kev30" wrote in message
...
Biff...Simply brilliant. Thanks for your super fast response. Thank you
so
much for your help. I needed it to reference a cell but was able to make
the
quick change.
=SUMPRODUCT(--(MMULT(--(ISNUMBER(SEARCH(D3,A$2:A$5462))),{1})0),B$2:B$54 62)

I did find one slight issue.
=SUMPRODUCT(--(MMULT(--(ISNUMBER(SEARCH(D3,A$2:A$5462))),{1})0),B$2:B$54 62)
works fine, but
=SUMPRODUCT(--(MMULT(--(ISNUMBER(SEARCH(D3,A$2:A$5463))),{1})0),B$2:B$54 63)
does not. Does it limit the lookup to 5462 rows or did I mess the whole
thing up by removing something?

If it's not too much to ask I would love to know what all this is doing.
In
particular the (--MMULT(--(ISNUMBER part of the formula. I have never
come
across this. Can you enlighten me or point me somewhere to enhance my
knowledge of this formula.

Thanks again Biff,
Kevin


"T. Valko" wrote:

If I understood what you want:

=SUMPRODUCT(--(MMULT(--(ISNUMBER(SEARCH({"red","blue","circle","square"}, A2:A9))),{1;1;1;1})0),B2:B9)

--
Biff
Microsoft Excel MVP


"Kev30" wrote in message
...
I would like to know if there is a way in excel to sumif based on if
the
data contains particular words. For example; column A has data like
(blue
circle, red circle, yellow circle, blue square, red square and so on.)
Column B has how many of each sold. I want to sum all the rows in
column
A
that contain a particular word (Circle, Square, Red, or Blue). I hope
this
makes sense and any help would be great.

Thanks, Kevin

What does the -- do

It converts the logical values TRUE and FALSE to 1 and 0 respectively.

This expression will return an array of either TRUE or FALSE:

ISNUMBER(SEARCH(D3,A$2:A$5462))

SUMPRODUCT calculates numbers so we have to convert those logicals to
numbers. The "--" is one way to do that.


--
Biff
Microsoft Excel MVP


"Kev30" wrote in message
...
This works even better, thank you. Sorry for the slight mix up about what
I
was looking for. I don't always give the best descriptions. I will have
a
need for looking up more than one word in the future so that will be very
helpful.

I have one last question if it's not to much to ask. What does the -- do
in =SUMPRODUCT(--(ISNUMBER(SEARCH(D3,A$2:A$5462))),B$2:B$5462)?

Thanks for all you help. I love to know that there is a place out there
that I can seek out answers for the questions I have.

Kevin

"T. Valko" wrote:

The formula I suggested was based on your post and my understanding that
you
seemed to want a sum if cells contained one of several words and the
samples
you posted contained more than one of those words.

If you're only searching for a single word:

I needed it to reference a cell

Then we can simplify things greatly:

=SUMPRODUCT(--(ISNUMBER(SEARCH(D3,A$2:A$5462))),B$2:B$5462)

Or, even:

=SUMIF(A2:5462,"*"&D3&"*",B2:B5462)

Does it limit the lookup to 5462 rows

The MMULT function is limited to 5461 rows. The version above is not
limited
except that you can't use entire columns as range references unless
you're
using Excel 2007.

MMULT = matrix multiplication

I would love to know what all this is doing. In
particular the (--MMULT(--(ISNUMBER part of the formula.

It's kind of hard to explain but here's an explanation I wrote a while
back
for someone else. The logic is the same but this example was for a
slightly
different request.

==========

For an explanation lets use a very small sample:

......A.....B.....C
1...1......5.....10

You want to count how many times 5 and 10 appear on the same row.

A10 = 5
B10 = 10

=SUMPRODUCT(--(MMULT(--(ISNUMBER(MATCH(A1:C1,A10:B10,0))),{1;1;1})=2))

This portion of the formula tests to see if any numbers in A1:C1 match
the
numbers in A10:B10

ISNUMBER(MATCH(A1:C1,A10:B10,0))

This will return a horizontal array of either TRUE or FALSE

FALSE TRUE TRUE

We need to convert those logical values to numbers. To do that we use the
double unary:

--(ISNUMBER(MATCH(A1:C1,A10:B10,0))

That will convert TRUE to 1 and FALSE to 0:

0 1 1

MMULT (matrix multiplication) is then used to return the count of matches
per row. We multiply the horizontal array

0 1 1 by a vertical array equal to the number of columns in the data
set.
In this case we have 3 columns A1:C1, so the vertical array is {1;1;1}

It would look something like this:

0..1..1.......1
..................1
..................1

The 0 times the top vertical 1
The middle horizontal 1 times the middle vertical 1
The rightmost horizontal 1 times the bottom vertical 1

The result would be:

0*1 + 1*1 + 1*1
0 + 1 + 1 = 2

Now, imagine your sample with 3 rows of data. The MMULT function would
return a count like that above for each row. Those counts are then passed
to
the SUMPRODUCT function where they are tested to see if they equal 2 (for
2
matches: A1:C1 matches A10 and A1:C1 matches B10).

This is also an array of TRUE or FALSE. Then we once again convert those
logical values to 1 and 0 then the SUMPRODUCT totals those to arrive at
the
final result of 1 (based on this explanation sample).

==========


--
Biff
Microsoft Excel MVP


"Kev30" wrote in message
...
Biff...Simply brilliant. Thanks for your super fast response. Thank
you
so
much for your help. I needed it to reference a cell but was able to
make
the
quick change.
=SUMPRODUCT(--(MMULT(--(ISNUMBER(SEARCH(D3,A$2:A$5462))),{1})0),B$2:B$54 62)

I did find one slight issue.
=SUMPRODUCT(--(MMULT(--(ISNUMBER(SEARCH(D3,A$2:A$5462))),{1})0),B$2:B$54 62)
works fine, but
=SUMPRODUCT(--(MMULT(--(ISNUMBER(SEARCH(D3,A$2:A$5463))),{1})0),B$2:B$54 63)
does not. Does it limit the lookup to 5462 rows or did I mess the
whole
thing up by removing something?

If it's not too much to ask I would love to know what all this is
doing.
In
particular the (--MMULT(--(ISNUMBER part of the formula. I have never
come
across this. Can you enlighten me or point me somewhere to enhance my
knowledge of this formula.

Thanks again Biff,
Kevin


"T. Valko" wrote:

If I understood what you want:

=SUMPRODUCT(--(MMULT(--(ISNUMBER(SEARCH({"red","blue","circle","square"}, A2:A9))),{1;1;1;1})0),B2:B9)

--
Biff
Microsoft Excel MVP


"Kev30" wrote in message
...
I would like to know if there is a way in excel to sumif based on if
the
data contains particular words. For example; column A has data like
(blue
circle, red circle, yellow circle, blue square, red square and so
on.)
Column B has how many of each sold. I want to sum all the rows in
column
A
that contain a particular word (Circle, Square, Red, or Blue). I
hope
this
makes sense and any help would be great.

Thanks, Kevin

Thanks for all your help!

"T. Valko" wrote:

What does the -- do

It converts the logical values TRUE and FALSE to 1 and 0 respectively.

This expression will return an array of either TRUE or FALSE:

ISNUMBER(SEARCH(D3,A$2:A$5462))

SUMPRODUCT calculates numbers so we have to convert those logicals to
numbers. The "--" is one way to do that.


--
Biff
Microsoft Excel MVP


"Kev30" wrote in message
...
This works even better, thank you. Sorry for the slight mix up about what
I
was looking for. I don't always give the best descriptions. I will have
a
need for looking up more than one word in the future so that will be very
helpful.

I have one last question if it's not to much to ask. What does the -- do
in =SUMPRODUCT(--(ISNUMBER(SEARCH(D3,A$2:A$5462))),B$2:B$5462)?

Thanks for all you help. I love to know that there is a place out there
that I can seek out answers for the questions I have.

Kevin

"T. Valko" wrote:

The formula I suggested was based on your post and my understanding that
you
seemed to want a sum if cells contained one of several words and the
samples
you posted contained more than one of those words.

If you're only searching for a single word:

I needed it to reference a cell

Then we can simplify things greatly:

=SUMPRODUCT(--(ISNUMBER(SEARCH(D3,A$2:A$5462))),B$2:B$5462)

Or, even:

=SUMIF(A2:5462,"*"&D3&"*",B2:B5462)

Does it limit the lookup to 5462 rows

The MMULT function is limited to 5461 rows. The version above is not
limited
except that you can't use entire columns as range references unless
you're
using Excel 2007.

MMULT = matrix multiplication

I would love to know what all this is doing. In
particular the (--MMULT(--(ISNUMBER part of the formula.

It's kind of hard to explain but here's an explanation I wrote a while
back
for someone else. The logic is the same but this example was for a
slightly
different request.

==========

For an explanation lets use a very small sample:

......A.....B.....C
1...1......5.....10

You want to count how many times 5 and 10 appear on the same row.

A10 = 5
B10 = 10

=SUMPRODUCT(--(MMULT(--(ISNUMBER(MATCH(A1:C1,A10:B10,0))),{1;1;1})=2))

This portion of the formula tests to see if any numbers in A1:C1 match
the
numbers in A10:B10

ISNUMBER(MATCH(A1:C1,A10:B10,0))

This will return a horizontal array of either TRUE or FALSE

FALSE TRUE TRUE

We need to convert those logical values to numbers. To do that we use the
double unary:

--(ISNUMBER(MATCH(A1:C1,A10:B10,0))

That will convert TRUE to 1 and FALSE to 0:

0 1 1

MMULT (matrix multiplication) is then used to return the count of matches
per row. We multiply the horizontal array

0 1 1 by a vertical array equal to the number of columns in the data
set.
In this case we have 3 columns A1:C1, so the vertical array is {1;1;1}

It would look something like this:

0..1..1.......1
..................1
..................1

The 0 times the top vertical 1
The middle horizontal 1 times the middle vertical 1
The rightmost horizontal 1 times the bottom vertical 1

The result would be:

0*1 + 1*1 + 1*1
0 + 1 + 1 = 2

Now, imagine your sample with 3 rows of data. The MMULT function would
return a count like that above for each row. Those counts are then passed
to
the SUMPRODUCT function where they are tested to see if they equal 2 (for
2
matches: A1:C1 matches A10 and A1:C1 matches B10).

This is also an array of TRUE or FALSE. Then we once again convert those
logical values to 1 and 0 then the SUMPRODUCT totals those to arrive at
the
final result of 1 (based on this explanation sample).

==========


--
Biff
Microsoft Excel MVP


"Kev30" wrote in message
...
Biff...Simply brilliant. Thanks for your super fast response. Thank
you
so
much for your help. I needed it to reference a cell but was able to
make
the
quick change.
=SUMPRODUCT(--(MMULT(--(ISNUMBER(SEARCH(D3,A$2:A$5462))),{1})0),B$2:B$54 62)

I did find one slight issue.
=SUMPRODUCT(--(MMULT(--(ISNUMBER(SEARCH(D3,A$2:A$5462))),{1})0),B$2:B$54 62)
works fine, but
=SUMPRODUCT(--(MMULT(--(ISNUMBER(SEARCH(D3,A$2:A$5463))),{1})0),B$2:B$54 63)
does not. Does it limit the lookup to 5462 rows or did I mess the
whole
thing up by removing something?

If it's not too much to ask I would love to know what all this is
doing.
In
particular the (--MMULT(--(ISNUMBER part of the formula. I have never
come
across this. Can you enlighten me or point me somewhere to enhance my
knowledge of this formula.

Thanks again Biff,
Kevin


"T. Valko" wrote:

If I understood what you want:

=SUMPRODUCT(--(MMULT(--(ISNUMBER(SEARCH({"red","blue","circle","square"}, A2:A9))),{1;1;1;1})0),B2:B9)

--
Biff
Microsoft Excel MVP


"Kev30" wrote in message
...
I would like to know if there is a way in excel to sumif based on if
the
data contains particular words. For example; column A has data like
(blue
circle, red circle, yellow circle, blue square, red square and so
on.)
Column B has how many of each sold. I want to sum all the rows in
column
A
that contain a particular word (Circle, Square, Red, or Blue). I
hope
this
makes sense and any help would be great.

Thanks, Kevin

You're welcome!

--
Biff
Microsoft Excel MVP


"Kev30" wrote in message
...
Thanks for all your help!

"T. Valko" wrote:

What does the -- do

It converts the logical values TRUE and FALSE to 1 and 0 respectively.

This expression will return an array of either TRUE or FALSE:

ISNUMBER(SEARCH(D3,A$2:A$5462))

SUMPRODUCT calculates numbers so we have to convert those logicals to
numbers. The "--" is one way to do that.


--
Biff
Microsoft Excel MVP


"Kev30" wrote in message
...
This works even better, thank you. Sorry for the slight mix up about
what
I
was looking for. I don't always give the best descriptions. I will
have
a
need for looking up more than one word in the future so that will be
very
helpful.

I have one last question if it's not to much to ask. What does the --
do
in =SUMPRODUCT(--(ISNUMBER(SEARCH(D3,A$2:A$5462))),B$2:B$5462)?

Thanks for all you help. I love to know that there is a place out
there
that I can seek out answers for the questions I have.

Kevin

"T. Valko" wrote:

The formula I suggested was based on your post and my understanding
that
you
seemed to want a sum if cells contained one of several words and the
samples
you posted contained more than one of those words.

If you're only searching for a single word:

I needed it to reference a cell

Then we can simplify things greatly:

=SUMPRODUCT(--(ISNUMBER(SEARCH(D3,A$2:A$5462))),B$2:B$5462)

Or, even:

=SUMIF(A2:5462,"*"&D3&"*",B2:B5462)

Does it limit the lookup to 5462 rows

The MMULT function is limited to 5461 rows. The version above is not
limited
except that you can't use entire columns as range references unless
you're
using Excel 2007.

MMULT = matrix multiplication

I would love to know what all this is doing. In
particular the (--MMULT(--(ISNUMBER part of the formula.

It's kind of hard to explain but here's an explanation I wrote a while
back
for someone else. The logic is the same but this example was for a
slightly
different request.

==========

For an explanation lets use a very small sample:

......A.....B.....C
1...1......5.....10

You want to count how many times 5 and 10 appear on the same row.

A10 = 5
B10 = 10

=SUMPRODUCT(--(MMULT(--(ISNUMBER(MATCH(A1:C1,A10:B10,0))),{1;1;1})=2))

This portion of the formula tests to see if any numbers in A1:C1 match
the
numbers in A10:B10

ISNUMBER(MATCH(A1:C1,A10:B10,0))

This will return a horizontal array of either TRUE or FALSE

FALSE TRUE TRUE

We need to convert those logical values to numbers. To do that we use
the
double unary:

--(ISNUMBER(MATCH(A1:C1,A10:B10,0))

That will convert TRUE to 1 and FALSE to 0:

0 1 1

MMULT (matrix multiplication) is then used to return the count of
matches
per row. We multiply the horizontal array

0 1 1 by a vertical array equal to the number of columns in the data
set.
In this case we have 3 columns A1:C1, so the vertical array is {1;1;1}

It would look something like this:

0..1..1.......1
..................1
..................1

The 0 times the top vertical 1
The middle horizontal 1 times the middle vertical 1
The rightmost horizontal 1 times the bottom vertical 1

The result would be:

0*1 + 1*1 + 1*1
0 + 1 + 1 = 2

Now, imagine your sample with 3 rows of data. The MMULT function would
return a count like that above for each row. Those counts are then
passed
to
the SUMPRODUCT function where they are tested to see if they equal 2
(for
2
matches: A1:C1 matches A10 and A1:C1 matches B10).

This is also an array of TRUE or FALSE. Then we once again convert
those
logical values to 1 and 0 then the SUMPRODUCT totals those to arrive
at
the
final result of 1 (based on this explanation sample).

==========


--
Biff
Microsoft Excel MVP


"Kev30" wrote in message
...
Biff...Simply brilliant. Thanks for your super fast response. Thank
you
so
much for your help. I needed it to reference a cell but was able to
make
the
quick change.
=SUMPRODUCT(--(MMULT(--(ISNUMBER(SEARCH(D3,A$2:A$5462))),{1})0),B$2:B$54 62)

I did find one slight issue.
=SUMPRODUCT(--(MMULT(--(ISNUMBER(SEARCH(D3,A$2:A$5462))),{1})0),B$2:B$54 62)
works fine, but
=SUMPRODUCT(--(MMULT(--(ISNUMBER(SEARCH(D3,A$2:A$5463))),{1})0),B$2:B$54 63)
does not. Does it limit the lookup to 5462 rows or did I mess the
whole
thing up by removing something?

If it's not too much to ask I would love to know what all this is
doing.
In
particular the (--MMULT(--(ISNUMBER part of the formula. I have
never
come
across this. Can you enlighten me or point me somewhere to enhance
my
knowledge of this formula.

Thanks again Biff,
Kevin


"T. Valko" wrote:

If I understood what you want:

=SUMPRODUCT(--(MMULT(--(ISNUMBER(SEARCH({"red","blue","circle","square"}, A2:A9))),{1;1;1;1})0),B2:B9)

--
Biff
Microsoft Excel MVP


"Kev30" wrote in message
...
I would like to know if there is a way in excel to sumif based on
if
the
data contains particular words. For example; column A has data
like
(blue
circle, red circle, yellow circle, blue square, red square and so
on.)
Column B has how many of each sold. I want to sum all the rows
in
column
A
that contain a particular word (Circle, Square, Red, or Blue). I
hope
this
makes sense and any help would be great.

Thanks, Kevin