find text string to use in a sumproduct
 

Hello,

I would like to create a sumproduct function that checks whether column a
contains the letter "M" and then if column f contains the text string "test"
to return the sum. The problem is column f contains such things as "TEST",
"test(A)", "test-20/1/10" and "testing". I want to count all of these.
--
Thanks in advance for any suggestions,
MarkN

find text string to use in a sumproduct
 

Try
=SUMPRODUCT((A1:A20="m")*(ISNUMBER(SEARCH("test",F 1:F20))))

--
Jacob (MVP - Excel)


"MarkN" wrote:

Hello,

I would like to create a sumproduct function that checks whether column a
contains the letter "M" and then if column f contains the text string "test"
to return the sum. The problem is column f contains such things as "TEST",
"test(A)", "test-20/1/10" and "testing". I want to count all of these.
--
Thanks in advance for any suggestions,
MarkN

find text string to use in a sumproduct
 

The following formula will perform a count of all occurences you mention...
however you didn't mention the column you want to subsequently sum if these 2
conditions return true, so add this column as the 3rd argument in the
sumproduct function:

=SUMPRODUCT(--(ISNUMBER(SEARCH("m",A1:A20))),--(ISNUMBER(SEARCH("test",F1:F20))))


--
Regards,
Dave


"MarkN" wrote:

Hello,

I would like to create a sumproduct function that checks whether column a
contains the letter "M" and then if column f contains the text string "test"
to return the sum. The problem is column f contains such things as "TEST",
"test(A)", "test-20/1/10" and "testing". I want to count all of these.
--
Thanks in advance for any suggestions,
MarkN

find text string to use in a sumproduct
 

Thanks to you both for your prompt replies, works like a charm.
--
Cheers,
MarkN


"David Billigmeier" wrote:

The following formula will perform a count of all occurences you mention...
however you didn't mention the column you want to subsequently sum if these 2
conditions return true, so add this column as the 3rd argument in the
sumproduct function:

=SUMPRODUCT(--(ISNUMBER(SEARCH("m",A1:A20))),--(ISNUMBER(SEARCH("test",F1:F20))))


--
Regards,
Dave


"MarkN" wrote:

Hello,

I would like to create a sumproduct function that checks whether column a
contains the letter "M" and then if column f contains the text string "test"
to return the sum. The problem is column f contains such things as "TEST",
"test(A)", "test-20/1/10" and "testing". I want to count all of these.
--
Thanks in advance for any suggestions,
MarkN