Wild card in formula
 

Greetings all. I am trying to use a SUMPRODUCT formula to calculate a
number, but I need to be able to use * or LIKE, or something with the same
results. I have the following, and of course it does not work

=SUMPRODUCT((A6:A100 = "*COURSES")*(B6:B100))

I will have several more columns involved once I get this part to work, thus
the SUMPRODUCT. Anyhow, I need it to sum B6:B100 where A6:A100 contains the
word "courses". I saw another post that kind of did something similar using
SEARCH(), but I could not get it to work. Thank you.

Greg

Wild card in formula
 

This is what I had in mind, but I can't get it to work:

=SUMPRODUCT((=IF(SEARCH("COURSES",A6:A100,1) < 0,1,0))*(B6:B100))

"Greg Snidow" wrote:

Greetings all. I am trying to use a SUMPRODUCT formula to calculate a
number, but I need to be able to use * or LIKE, or something with the same
results. I have the following, and of course it does not work

=SUMPRODUCT((A6:A100 = "*COURSES")*(B6:B100))

I will have several more columns involved once I get this part to work, thus
the SUMPRODUCT. Anyhow, I need it to sum B6:B100 where A6:A100 contains the
word "courses". I saw another post that kind of did something similar using
SEARCH(), but I could not get it to work. Thank you.

Greg

Wild card in formula
 

Try

=SUM(IF(ISERROR(SEARCH("COURSES",A6:A100)),0,B6:B1 00))

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

Hope this helps,

Hutch

"Greg Snidow" wrote:

Greetings all. I am trying to use a SUMPRODUCT formula to calculate a
number, but I need to be able to use * or LIKE, or something with the same
results. I have the following, and of course it does not work

=SUMPRODUCT((A6:A100 = "*COURSES")*(B6:B100))

I will have several more columns involved once I get this part to work, thus
the SUMPRODUCT. Anyhow, I need it to sum B6:B100 where A6:A100 contains the
word "courses". I saw another post that kind of did something similar using
SEARCH(), but I could not get it to work. Thank you.

Greg

Wild card in formula
 

This should work...

=SUMPRODUCT((RIGHT(A6:A100,7) = "COURSES")*(B6:B100))

--
Rick (MVP - Excel)


"Greg Snidow" wrote in message
...
Greetings all. I am trying to use a SUMPRODUCT formula to calculate a
number, but I need to be able to use * or LIKE, or something with the same
results. I have the following, and of course it does not work

=SUMPRODUCT((A6:A100 = "*COURSES")*(B6:B100))

I will have several more columns involved once I get this part to work,
thus
the SUMPRODUCT. Anyhow, I need it to sum B6:B100 where A6:A100 contains
the
word "courses". I saw another post that kind of did something similar
using
SEARCH(), but I could not get it to work. Thank you.

Greg

Wild card in formula
 

Tom, that worked like a charm for just the two columns. Thank you. However,
I will need to include several more columns, so how could I incorporate this
using SUMPRODUCT?

"Tom Hutchins" wrote:

Try

=SUM(IF(ISERROR(SEARCH("COURSES",A6:A100)),0,B6:B1 00))

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

Hope this helps,

Hutch

"Greg Snidow" wrote:

Greetings all. I am trying to use a SUMPRODUCT formula to calculate a
number, but I need to be able to use * or LIKE, or something with the same
results. I have the following, and of course it does not work

=SUMPRODUCT((A6:A100 = "*COURSES")*(B6:B100))

I will have several more columns involved once I get this part to work, thus
the SUMPRODUCT. Anyhow, I need it to sum B6:B100 where A6:A100 contains the
word "courses". I saw another post that kind of did something similar using
SEARCH(), but I could not get it to work. Thank you.

Greg

Wild card in formula
 

I got it to work!

=SUMPRODUCT((IF(ISERROR(SEARCH("COURSES",A6:A100)) ,0,1)*(B6:B100)))

"Tom Hutchins" wrote:

Try

=SUM(IF(ISERROR(SEARCH("COURSES",A6:A100)),0,B6:B1 00))

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

Hope this helps,

Hutch

"Greg Snidow" wrote:

Greetings all. I am trying to use a SUMPRODUCT formula to calculate a
number, but I need to be able to use * or LIKE, or something with the same
results. I have the following, and of course it does not work

=SUMPRODUCT((A6:A100 = "*COURSES")*(B6:B100))

I will have several more columns involved once I get this part to work, thus
the SUMPRODUCT. Anyhow, I need it to sum B6:B100 where A6:A100 contains the
word "courses". I saw another post that kind of did something similar using
SEARCH(), but I could not get it to work. Thank you.

Greg

Wild card in formula
 

Dang it!!! I got it to work, but then I typed it again and I get the #N/A
error. Now when I type the exact same thing as I did first time, I get a
message saying "blah, blah...not enough parenthesis..." and it adds an extra
one at the end, and I can see it now where I copied and pasted below. Any
ideas? I am completely baffled.

"Greg Snidow" wrote:

I got it to work!

=SUMPRODUCT((IF(ISERROR(SEARCH("COURSES",A6:A100)) ,0,1)*(B6:B100)))

"Tom Hutchins" wrote:

Try

=SUM(IF(ISERROR(SEARCH("COURSES",A6:A100)),0,B6:B1 00))

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

Hope this helps,

Hutch

"Greg Snidow" wrote:

Greetings all. I am trying to use a SUMPRODUCT formula to calculate a
number, but I need to be able to use * or LIKE, or something with the same
results. I have the following, and of course it does not work

=SUMPRODUCT((A6:A100 = "*COURSES")*(B6:B100))

I will have several more columns involved once I get this part to work, thus
the SUMPRODUCT. Anyhow, I need it to sum B6:B100 where A6:A100 contains the
word "courses". I saw another post that kind of did something similar using
SEARCH(), but I could not get it to work. Thank you.

Greg

Wild card in formula
 

I had some text at the bottom of the new sheet in column A that was messing
it up. No problem.

"Greg Snidow" wrote:

Dang it!!! I got it to work, but then I typed it again and I get the #N/A
error. Now when I type the exact same thing as I did first time, I get a
message saying "blah, blah...not enough parenthesis..." and it adds an extra
one at the end, and I can see it now where I copied and pasted below. Any
ideas? I am completely baffled.

"Greg Snidow" wrote:

I got it to work!

=SUMPRODUCT((IF(ISERROR(SEARCH("COURSES",A6:A100)) ,0,1)*(B6:B100)))

"Tom Hutchins" wrote:

Try

=SUM(IF(ISERROR(SEARCH("COURSES",A6:A100)),0,B6:B1 00))

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

Hope this helps,

Hutch

"Greg Snidow" wrote:

Greetings all. I am trying to use a SUMPRODUCT formula to calculate a
number, but I need to be able to use * or LIKE, or something with the same
results. I have the following, and of course it does not work

=SUMPRODUCT((A6:A100 = "*COURSES")*(B6:B100))

I will have several more columns involved once I get this part to work, thus
the SUMPRODUCT. Anyhow, I need it to sum B6:B100 where A6:A100 contains the
word "courses". I saw another post that kind of did something similar using
SEARCH(), but I could not get it to work. Thank you.

Greg