Sumproduct based on substring?

[altopalo]

Hi -

I have a worksheet that has bug titles in column B and priority in column C.

We have developed standard naming conventions for the bug title. For example:

- MC: the Member Center has a bad help link in the header
- FP: the Front Page has a broken image in the header

I want to get a count of the bugs that contain a sub string of the title (e.g. "MC" or "FP") and also have a certain Priority level in column C.

I've tried these without success:

=SUMPRODUCT((B32:B1000="MC")*(C32:C1000="P1"))
=SUMPRODUCT((B32:B1000="*MC*")*(C32:C1000="P1"))
=SUMPRODUCT((B32:B1000=*MC*)*(C32:C1000="P1"))
=SUMPRODUCT((B32:B1000,*MC*)*(C32:C1000="P1"))

Any help would be greatly appreciated!!

[Biff]

Hi!

Try this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("MC:",B32:B1000))),--(C32:C100="P1"))

Better:

Use cells to hold the criteria:

A1 = MC:
B1 = P1

=SUMPRODUCT(--(ISNUMBER(SEARCH(A1,B32:B1000))),--(C32:C100=B1))

Including the colon with the string helps reduce any false positives.

Biff

"altopalo" <[email protected]> wrote in
message news:[email protected]...
>
> Hi -
>
> I have a worksheet that has bug titles in column B and priority in
> column C.
>
> We have developed standard naming conventions for the bug title. For
> example:
>
> - MC: the Member Center has a bad help link in the header
> - FP: the Front Page has a broken image in the header
>
> I want to get a count of the bugs that contain a sub string of the
> title (e.g. "MC" or "FP") and also have a certain Priority level in
> column C.
>
> I've tried these without success:
>
> =SUMPRODUCT((B32:B1000="MC")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000="*MC*")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000=*MC*)*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000,*MC*)*(C32:C1000="P1"))
>
> Any help would be greatly appreciated!!
>
>
> --
> altopalo
> ------------------------------------------------------------------------
> altopalo's Profile:
> http://www.excelforum.com/member.php...o&userid=25276
> View this thread: http://www.excelforum.com/showthread...hreadid=387679
>

[RagDyer]

Try this:

=SUMPRODUCT((ISNUMBER(SEARCH("MC",B32:B1000)))*(C32:C1000="P1"))
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"altopalo" <[email protected]> wrote in
message news:[email protected]...
>
> Hi -
>
> I have a worksheet that has bug titles in column B and priority in
> column C.
>
> We have developed standard naming conventions for the bug title. For
> example:
>
> - MC: the Member Center has a bad help link in the header
> - FP: the Front Page has a broken image in the header
>
> I want to get a count of the bugs that contain a sub string of the
> title (e.g. "MC" or "FP") and also have a certain Priority level in
> column C.
>
> I've tried these without success:
>
> =SUMPRODUCT((B32:B1000="MC")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000="*MC*")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000=*MC*)*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000,*MC*)*(C32:C1000="P1"))
>
> Any help would be greatly appreciated!!
>
>
> --
> altopalo
> ------------------------------------------------------------------------
> altopalo's Profile:
http://www.excelforum.com/member.php...o&userid=25276
> View this thread: http://www.excelforum.com/showthread...hreadid=387679
>

[altopalo]

Thank you!!!!


=SUMPRODUCT((ISNUMBER(SEARCH("MC",B32:B1000)))*(C32:C1000="P1"))

worked beautifully

[RagDyeR]

I guess the reason it worked beautifully was because it didn't contain a
typo, leaving out a "0".<vbg>

Appreciate the feed-back.

--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------


"altopalo" <[email protected]> wrote in
message news:[email protected]...

Thank you!!!!


=SUMPRODUCT((ISNUMBER(SEARCH("MC",B32:B1000)))*(C32:C1000="P1"))

worked beautifully


--
altopalo
------------------------------------------------------------------------
altopalo's Profile:
http://www.excelforum.com/member.php...o&userid=25276
View this thread: http://www.excelforum.com/showthread...hreadid=387679

[RagDyer]

Try this:

=SUMPRODUCT((ISNUMBER(SEARCH("MC",B32:B1000)))*(C32:C1000="P1"))
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"altopalo" <[email protected]> wrote in
message news:[email protected]...
>
> Hi -
>
> I have a worksheet that has bug titles in column B and priority in
> column C.
>
> We have developed standard naming conventions for the bug title. For
> example:
>
> - MC: the Member Center has a bad help link in the header
> - FP: the Front Page has a broken image in the header
>
> I want to get a count of the bugs that contain a sub string of the
> title (e.g. "MC" or "FP") and also have a certain Priority level in
> column C.
>
> I've tried these without success:
>
> =SUMPRODUCT((B32:B1000="MC")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000="*MC*")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000=*MC*)*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000,*MC*)*(C32:C1000="P1"))
>
> Any help would be greatly appreciated!!
>
>
> --
> altopalo
> ------------------------------------------------------------------------
> altopalo's Profile:
http://www.excelforum.com/member.php...o&userid=25276
> View this thread: http://www.excelforum.com/showthread...hreadid=387679
>

[RagDyeR]

I guess the reason it worked beautifully was because it didn't contain a
typo, leaving out a "0".<vbg>

Appreciate the feed-back.

--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------


"altopalo" <[email protected]> wrote in
message news:[email protected]...

Thank you!!!!


=SUMPRODUCT((ISNUMBER(SEARCH("MC",B32:B1000)))*(C32:C1000="P1"))

worked beautifully


--
altopalo
------------------------------------------------------------------------
altopalo's Profile:
http://www.excelforum.com/member.php...o&userid=25276
View this thread: http://www.excelforum.com/showthread...hreadid=387679

[Biff]

Hi!

Try this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("MC:",B32:B1000))),--(C32:C100="P1"))

Better:

Use cells to hold the criteria:

A1 = MC:
B1 = P1

=SUMPRODUCT(--(ISNUMBER(SEARCH(A1,B32:B1000))),--(C32:C100=B1))

Including the colon with the string helps reduce any false positives.

Biff

"altopalo" <[email protected]> wrote in
message news:[email protected]...
>
> Hi -
>
> I have a worksheet that has bug titles in column B and priority in
> column C.
>
> We have developed standard naming conventions for the bug title. For
> example:
>
> - MC: the Member Center has a bad help link in the header
> - FP: the Front Page has a broken image in the header
>
> I want to get a count of the bugs that contain a sub string of the
> title (e.g. "MC" or "FP") and also have a certain Priority level in
> column C.
>
> I've tried these without success:
>
> =SUMPRODUCT((B32:B1000="MC")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000="*MC*")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000=*MC*)*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000,*MC*)*(C32:C1000="P1"))
>
> Any help would be greatly appreciated!!
>
>
> --
> altopalo
> ------------------------------------------------------------------------
> altopalo's Profile:
> http://www.excelforum.com/member.php...o&userid=25276
> View this thread: http://www.excelforum.com/showthread...hreadid=387679
>

[Biff]

Hi!

Try this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("MC:",B32:B1000))),--(C32:C100="P1"))

Better:

Use cells to hold the criteria:

A1 = MC:
B1 = P1

=SUMPRODUCT(--(ISNUMBER(SEARCH(A1,B32:B1000))),--(C32:C100=B1))

Including the colon with the string helps reduce any false positives.

Biff

"altopalo" <[email protected]> wrote in
message news:[email protected]...
>
> Hi -
>
> I have a worksheet that has bug titles in column B and priority in
> column C.
>
> We have developed standard naming conventions for the bug title. For
> example:
>
> - MC: the Member Center has a bad help link in the header
> - FP: the Front Page has a broken image in the header
>
> I want to get a count of the bugs that contain a sub string of the
> title (e.g. "MC" or "FP") and also have a certain Priority level in
> column C.
>
> I've tried these without success:
>
> =SUMPRODUCT((B32:B1000="MC")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000="*MC*")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000=*MC*)*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000,*MC*)*(C32:C1000="P1"))
>
> Any help would be greatly appreciated!!
>
>
> --
> altopalo
> ------------------------------------------------------------------------
> altopalo's Profile:
> http://www.excelforum.com/member.php...o&userid=25276
> View this thread: http://www.excelforum.com/showthread...hreadid=387679
>

[RagDyer]

Try this:

=SUMPRODUCT((ISNUMBER(SEARCH("MC",B32:B1000)))*(C32:C1000="P1"))
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"altopalo" <[email protected]> wrote in
message news:[email protected]...
>
> Hi -
>
> I have a worksheet that has bug titles in column B and priority in
> column C.
>
> We have developed standard naming conventions for the bug title. For
> example:
>
> - MC: the Member Center has a bad help link in the header
> - FP: the Front Page has a broken image in the header
>
> I want to get a count of the bugs that contain a sub string of the
> title (e.g. "MC" or "FP") and also have a certain Priority level in
> column C.
>
> I've tried these without success:
>
> =SUMPRODUCT((B32:B1000="MC")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000="*MC*")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000=*MC*)*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000,*MC*)*(C32:C1000="P1"))
>
> Any help would be greatly appreciated!!
>
>
> --
> altopalo
> ------------------------------------------------------------------------
> altopalo's Profile:
http://www.excelforum.com/member.php...o&userid=25276
> View this thread: http://www.excelforum.com/showthread...hreadid=387679
>

[RagDyeR]

I guess the reason it worked beautifully was because it didn't contain a
typo, leaving out a "0".<vbg>

Appreciate the feed-back.

--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------


"altopalo" <[email protected]> wrote in
message news:[email protected]...

Thank you!!!!


=SUMPRODUCT((ISNUMBER(SEARCH("MC",B32:B1000)))*(C32:C1000="P1"))

worked beautifully


--
altopalo
------------------------------------------------------------------------
altopalo's Profile:
http://www.excelforum.com/member.php...o&userid=25276
View this thread: http://www.excelforum.com/showthread...hreadid=387679

[Biff]

Hi!

Try this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("MC:",B32:B1000))),--(C32:C100="P1"))

Better:

Use cells to hold the criteria:

A1 = MC:
B1 = P1

=SUMPRODUCT(--(ISNUMBER(SEARCH(A1,B32:B1000))),--(C32:C100=B1))

Including the colon with the string helps reduce any false positives.

Biff

"altopalo" <[email protected]> wrote in
message news:[email protected]...
>
> Hi -
>
> I have a worksheet that has bug titles in column B and priority in
> column C.
>
> We have developed standard naming conventions for the bug title. For
> example:
>
> - MC: the Member Center has a bad help link in the header
> - FP: the Front Page has a broken image in the header
>
> I want to get a count of the bugs that contain a sub string of the
> title (e.g. "MC" or "FP") and also have a certain Priority level in
> column C.
>
> I've tried these without success:
>
> =SUMPRODUCT((B32:B1000="MC")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000="*MC*")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000=*MC*)*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000,*MC*)*(C32:C1000="P1"))
>
> Any help would be greatly appreciated!!
>
>
> --
> altopalo
> ------------------------------------------------------------------------
> altopalo's Profile:
> http://www.excelforum.com/member.php...o&userid=25276
> View this thread: http://www.excelforum.com/showthread...hreadid=387679
>

[RagDyeR]

I guess the reason it worked beautifully was because it didn't contain a
typo, leaving out a "0".<vbg>

Appreciate the feed-back.

--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------


"altopalo" <[email protected]> wrote in
message news:[email protected]...

Thank you!!!!


=SUMPRODUCT((ISNUMBER(SEARCH("MC",B32:B1000)))*(C32:C1000="P1"))

worked beautifully


--
altopalo
------------------------------------------------------------------------
altopalo's Profile:
http://www.excelforum.com/member.php...o&userid=25276
View this thread: http://www.excelforum.com/showthread...hreadid=387679

[RagDyer]

Try this:

=SUMPRODUCT((ISNUMBER(SEARCH("MC",B32:B1000)))*(C32:C1000="P1"))
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"altopalo" <[email protected]> wrote in
message news:[email protected]...
>
> Hi -
>
> I have a worksheet that has bug titles in column B and priority in
> column C.
>
> We have developed standard naming conventions for the bug title. For
> example:
>
> - MC: the Member Center has a bad help link in the header
> - FP: the Front Page has a broken image in the header
>
> I want to get a count of the bugs that contain a sub string of the
> title (e.g. "MC" or "FP") and also have a certain Priority level in
> column C.
>
> I've tried these without success:
>
> =SUMPRODUCT((B32:B1000="MC")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000="*MC*")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000=*MC*)*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000,*MC*)*(C32:C1000="P1"))
>
> Any help would be greatly appreciated!!
>
>
> --
> altopalo
> ------------------------------------------------------------------------
> altopalo's Profile:
http://www.excelforum.com/member.php...o&userid=25276
> View this thread: http://www.excelforum.com/showthread...hreadid=387679
>

[RagDyer]

Try this:

=SUMPRODUCT((ISNUMBER(SEARCH("MC",B32:B1000)))*(C32:C1000="P1"))
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"altopalo" <[email protected]> wrote in
message news:[email protected]...
>
> Hi -
>
> I have a worksheet that has bug titles in column B and priority in
> column C.
>
> We have developed standard naming conventions for the bug title. For
> example:
>
> - MC: the Member Center has a bad help link in the header
> - FP: the Front Page has a broken image in the header
>
> I want to get a count of the bugs that contain a sub string of the
> title (e.g. "MC" or "FP") and also have a certain Priority level in
> column C.
>
> I've tried these without success:
>
> =SUMPRODUCT((B32:B1000="MC")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000="*MC*")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000=*MC*)*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000,*MC*)*(C32:C1000="P1"))
>
> Any help would be greatly appreciated!!
>
>
> --
> altopalo
> ------------------------------------------------------------------------
> altopalo's Profile:
http://www.excelforum.com/member.php...o&userid=25276
> View this thread: http://www.excelforum.com/showthread...hreadid=387679
>

[Biff]

Hi!

Try this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("MC:",B32:B1000))),--(C32:C100="P1"))

Better:

Use cells to hold the criteria:

A1 = MC:
B1 = P1

=SUMPRODUCT(--(ISNUMBER(SEARCH(A1,B32:B1000))),--(C32:C100=B1))

Including the colon with the string helps reduce any false positives.

Biff

"altopalo" <[email protected]> wrote in
message news:[email protected]...
>
> Hi -
>
> I have a worksheet that has bug titles in column B and priority in
> column C.
>
> We have developed standard naming conventions for the bug title. For
> example:
>
> - MC: the Member Center has a bad help link in the header
> - FP: the Front Page has a broken image in the header
>
> I want to get a count of the bugs that contain a sub string of the
> title (e.g. "MC" or "FP") and also have a certain Priority level in
> column C.
>
> I've tried these without success:
>
> =SUMPRODUCT((B32:B1000="MC")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000="*MC*")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000=*MC*)*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000,*MC*)*(C32:C1000="P1"))
>
> Any help would be greatly appreciated!!
>
>
> --
> altopalo
> ------------------------------------------------------------------------
> altopalo's Profile:
> http://www.excelforum.com/member.php...o&userid=25276
> View this thread: http://www.excelforum.com/showthread...hreadid=387679
>

[RagDyeR]

I guess the reason it worked beautifully was because it didn't contain a
typo, leaving out a "0".<vbg>

Appreciate the feed-back.

--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------


"altopalo" <[email protected]> wrote in
message news:[email protected]...

Thank you!!!!


=SUMPRODUCT((ISNUMBER(SEARCH("MC",B32:B1000)))*(C32:C1000="P1"))

worked beautifully


--
altopalo
------------------------------------------------------------------------
altopalo's Profile:
http://www.excelforum.com/member.php...o&userid=25276
View this thread: http://www.excelforum.com/showthread...hreadid=387679

[RagDyeR]

I guess the reason it worked beautifully was because it didn't contain a
typo, leaving out a "0".<vbg>

Appreciate the feed-back.

--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------


"altopalo" <[email protected]> wrote in
message news:[email protected]...

Thank you!!!!


=SUMPRODUCT((ISNUMBER(SEARCH("MC",B32:B1000)))*(C32:C1000="P1"))

worked beautifully


--
altopalo
------------------------------------------------------------------------
altopalo's Profile:
http://www.excelforum.com/member.php...o&userid=25276
View this thread: http://www.excelforum.com/showthread...hreadid=387679

[Biff]

Hi!

Try this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("MC:",B32:B1000))),--(C32:C100="P1"))

Better:

Use cells to hold the criteria:

A1 = MC:
B1 = P1

=SUMPRODUCT(--(ISNUMBER(SEARCH(A1,B32:B1000))),--(C32:C100=B1))

Including the colon with the string helps reduce any false positives.

Biff

"altopalo" <[email protected]> wrote in
message news:[email protected]...
>
> Hi -
>
> I have a worksheet that has bug titles in column B and priority in
> column C.
>
> We have developed standard naming conventions for the bug title. For
> example:
>
> - MC: the Member Center has a bad help link in the header
> - FP: the Front Page has a broken image in the header
>
> I want to get a count of the bugs that contain a sub string of the
> title (e.g. "MC" or "FP") and also have a certain Priority level in
> column C.
>
> I've tried these without success:
>
> =SUMPRODUCT((B32:B1000="MC")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000="*MC*")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000=*MC*)*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000,*MC*)*(C32:C1000="P1"))
>
> Any help would be greatly appreciated!!
>
>
> --
> altopalo
> ------------------------------------------------------------------------
> altopalo's Profile:
> http://www.excelforum.com/member.php...o&userid=25276
> View this thread: http://www.excelforum.com/showthread...hreadid=387679
>

[RagDyer]

Try this:

=SUMPRODUCT((ISNUMBER(SEARCH("MC",B32:B1000)))*(C32:C1000="P1"))
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"altopalo" <[email protected]> wrote in
message news:[email protected]...
>
> Hi -
>
> I have a worksheet that has bug titles in column B and priority in
> column C.
>
> We have developed standard naming conventions for the bug title. For
> example:
>
> - MC: the Member Center has a bad help link in the header
> - FP: the Front Page has a broken image in the header
>
> I want to get a count of the bugs that contain a sub string of the
> title (e.g. "MC" or "FP") and also have a certain Priority level in
> column C.
>
> I've tried these without success:
>
> =SUMPRODUCT((B32:B1000="MC")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000="*MC*")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000=*MC*)*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000,*MC*)*(C32:C1000="P1"))
>
> Any help would be greatly appreciated!!
>
>
> --
> altopalo
> ------------------------------------------------------------------------
> altopalo's Profile:
http://www.excelforum.com/member.php...o&userid=25276
> View this thread: http://www.excelforum.com/showthread...hreadid=387679
>

[RagDyer]

Try this:

=SUMPRODUCT((ISNUMBER(SEARCH("MC",B32:B1000)))*(C32:C1000="P1"))
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"altopalo" <[email protected]> wrote in
message news:[email protected]...
>
> Hi -
>
> I have a worksheet that has bug titles in column B and priority in
> column C.
>
> We have developed standard naming conventions for the bug title. For
> example:
>
> - MC: the Member Center has a bad help link in the header
> - FP: the Front Page has a broken image in the header
>
> I want to get a count of the bugs that contain a sub string of the
> title (e.g. "MC" or "FP") and also have a certain Priority level in
> column C.
>
> I've tried these without success:
>
> =SUMPRODUCT((B32:B1000="MC")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000="*MC*")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000=*MC*)*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000,*MC*)*(C32:C1000="P1"))
>
> Any help would be greatly appreciated!!
>
>
> --
> altopalo
> ------------------------------------------------------------------------
> altopalo's Profile:
http://www.excelforum.com/member.php...o&userid=25276
> View this thread: http://www.excelforum.com/showthread...hreadid=387679
>

[RagDyeR]

I guess the reason it worked beautifully was because it didn't contain a
typo, leaving out a "0".<vbg>

Appreciate the feed-back.

--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------


"altopalo" <[email protected]> wrote in
message news:[email protected]...

Thank you!!!!


=SUMPRODUCT((ISNUMBER(SEARCH("MC",B32:B1000)))*(C32:C1000="P1"))

worked beautifully


--
altopalo
------------------------------------------------------------------------
altopalo's Profile:
http://www.excelforum.com/member.php...o&userid=25276
View this thread: http://www.excelforum.com/showthread...hreadid=387679

[Biff]

Hi!

Try this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("MC:",B32:B1000))),--(C32:C100="P1"))

Better:

Use cells to hold the criteria:

A1 = MC:
B1 = P1

=SUMPRODUCT(--(ISNUMBER(SEARCH(A1,B32:B1000))),--(C32:C100=B1))

Including the colon with the string helps reduce any false positives.

Biff

"altopalo" <[email protected]> wrote in
message news:[email protected]...
>
> Hi -
>
> I have a worksheet that has bug titles in column B and priority in
> column C.
>
> We have developed standard naming conventions for the bug title. For
> example:
>
> - MC: the Member Center has a bad help link in the header
> - FP: the Front Page has a broken image in the header
>
> I want to get a count of the bugs that contain a sub string of the
> title (e.g. "MC" or "FP") and also have a certain Priority level in
> column C.
>
> I've tried these without success:
>
> =SUMPRODUCT((B32:B1000="MC")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000="*MC*")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000=*MC*)*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000,*MC*)*(C32:C1000="P1"))
>
> Any help would be greatly appreciated!!
>
>
> --
> altopalo
> ------------------------------------------------------------------------
> altopalo's Profile:
> http://www.excelforum.com/member.php...o&userid=25276
> View this thread: http://www.excelforum.com/showthread...hreadid=387679
>

[RagDyeR]

I guess the reason it worked beautifully was because it didn't contain a
typo, leaving out a "0".<vbg>

Appreciate the feed-back.

--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------


"altopalo" <[email protected]> wrote in
message news:[email protected]...

Thank you!!!!


=SUMPRODUCT((ISNUMBER(SEARCH("MC",B32:B1000)))*(C32:C1000="P1"))

worked beautifully


--
altopalo
------------------------------------------------------------------------
altopalo's Profile:
http://www.excelforum.com/member.php...o&userid=25276
View this thread: http://www.excelforum.com/showthread...hreadid=387679

[RagDyer]

Try this:

=SUMPRODUCT((ISNUMBER(SEARCH("MC",B32:B1000)))*(C32:C1000="P1"))
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"altopalo" <[email protected]> wrote in
message news:[email protected]...
>
> Hi -
>
> I have a worksheet that has bug titles in column B and priority in
> column C.
>
> We have developed standard naming conventions for the bug title. For
> example:
>
> - MC: the Member Center has a bad help link in the header
> - FP: the Front Page has a broken image in the header
>
> I want to get a count of the bugs that contain a sub string of the
> title (e.g. "MC" or "FP") and also have a certain Priority level in
> column C.
>
> I've tried these without success:
>
> =SUMPRODUCT((B32:B1000="MC")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000="*MC*")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000=*MC*)*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000,*MC*)*(C32:C1000="P1"))
>
> Any help would be greatly appreciated!!
>
>
> --
> altopalo
> ------------------------------------------------------------------------
> altopalo's Profile:
http://www.excelforum.com/member.php...o&userid=25276
> View this thread: http://www.excelforum.com/showthread...hreadid=387679
>

[Biff]

Hi!

Try this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("MC:",B32:B1000))),--(C32:C100="P1"))

Better:

Use cells to hold the criteria:

A1 = MC:
B1 = P1

=SUMPRODUCT(--(ISNUMBER(SEARCH(A1,B32:B1000))),--(C32:C100=B1))

Including the colon with the string helps reduce any false positives.

Biff

"altopalo" <[email protected]> wrote in
message news:[email protected]...
>
> Hi -
>
> I have a worksheet that has bug titles in column B and priority in
> column C.
>
> We have developed standard naming conventions for the bug title. For
> example:
>
> - MC: the Member Center has a bad help link in the header
> - FP: the Front Page has a broken image in the header
>
> I want to get a count of the bugs that contain a sub string of the
> title (e.g. "MC" or "FP") and also have a certain Priority level in
> column C.
>
> I've tried these without success:
>
> =SUMPRODUCT((B32:B1000="MC")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000="*MC*")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000=*MC*)*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000,*MC*)*(C32:C1000="P1"))
>
> Any help would be greatly appreciated!!
>
>
> --
> altopalo
> ------------------------------------------------------------------------
> altopalo's Profile:
> http://www.excelforum.com/member.php...o&userid=25276
> View this thread: http://www.excelforum.com/showthread...hreadid=387679
>

[RagDyeR]

I guess the reason it worked beautifully was because it didn't contain a
typo, leaving out a "0".<vbg>

Appreciate the feed-back.

--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------


"altopalo" <[email protected]> wrote in
message news:[email protected]...

Thank you!!!!


=SUMPRODUCT((ISNUMBER(SEARCH("MC",B32:B1000)))*(C32:C1000="P1"))

worked beautifully


--
altopalo
------------------------------------------------------------------------
altopalo's Profile:
http://www.excelforum.com/member.php...o&userid=25276
View this thread: http://www.excelforum.com/showthread...hreadid=387679

[RagDyer]

Try this:

=SUMPRODUCT((ISNUMBER(SEARCH("MC",B32:B1000)))*(C32:C1000="P1"))
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"altopalo" <[email protected]> wrote in
message news:[email protected]...
>
> Hi -
>
> I have a worksheet that has bug titles in column B and priority in
> column C.
>
> We have developed standard naming conventions for the bug title. For
> example:
>
> - MC: the Member Center has a bad help link in the header
> - FP: the Front Page has a broken image in the header
>
> I want to get a count of the bugs that contain a sub string of the
> title (e.g. "MC" or "FP") and also have a certain Priority level in
> column C.
>
> I've tried these without success:
>
> =SUMPRODUCT((B32:B1000="MC")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000="*MC*")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000=*MC*)*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000,*MC*)*(C32:C1000="P1"))
>
> Any help would be greatly appreciated!!
>
>
> --
> altopalo
> ------------------------------------------------------------------------
> altopalo's Profile:
http://www.excelforum.com/member.php...o&userid=25276
> View this thread: http://www.excelforum.com/showthread...hreadid=387679
>

[Biff]

Hi!

Try this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("MC:",B32:B1000))),--(C32:C100="P1"))

Better:

Use cells to hold the criteria:

A1 = MC:
B1 = P1

=SUMPRODUCT(--(ISNUMBER(SEARCH(A1,B32:B1000))),--(C32:C100=B1))

Including the colon with the string helps reduce any false positives.

Biff

"altopalo" <[email protected]> wrote in
message news:[email protected]...
>
> Hi -
>
> I have a worksheet that has bug titles in column B and priority in
> column C.
>
> We have developed standard naming conventions for the bug title. For
> example:
>
> - MC: the Member Center has a bad help link in the header
> - FP: the Front Page has a broken image in the header
>
> I want to get a count of the bugs that contain a sub string of the
> title (e.g. "MC" or "FP") and also have a certain Priority level in
> column C.
>
> I've tried these without success:
>
> =SUMPRODUCT((B32:B1000="MC")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000="*MC*")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000=*MC*)*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000,*MC*)*(C32:C1000="P1"))
>
> Any help would be greatly appreciated!!
>
>
> --
> altopalo
> ------------------------------------------------------------------------
> altopalo's Profile:
> http://www.excelforum.com/member.php...o&userid=25276
> View this thread: http://www.excelforum.com/showthread...hreadid=387679
>

[RagDyeR]

I guess the reason it worked beautifully was because it didn't contain a
typo, leaving out a "0".<vbg>

Appreciate the feed-back.

--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------


"altopalo" <[email protected]> wrote in
message news:[email protected]...

Thank you!!!!


=SUMPRODUCT((ISNUMBER(SEARCH("MC",B32:B1000)))*(C32:C1000="P1"))

worked beautifully


--
altopalo
------------------------------------------------------------------------
altopalo's Profile:
http://www.excelforum.com/member.php...o&userid=25276
View this thread: http://www.excelforum.com/showthread...hreadid=387679

[RagDyer]

Try this:

=SUMPRODUCT((ISNUMBER(SEARCH("MC",B32:B1000)))*(C32:C1000="P1"))
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"altopalo" <[email protected]> wrote in
message news:[email protected]...
>
> Hi -
>
> I have a worksheet that has bug titles in column B and priority in
> column C.
>
> We have developed standard naming conventions for the bug title. For
> example:
>
> - MC: the Member Center has a bad help link in the header
> - FP: the Front Page has a broken image in the header
>
> I want to get a count of the bugs that contain a sub string of the
> title (e.g. "MC" or "FP") and also have a certain Priority level in
> column C.
>
> I've tried these without success:
>
> =SUMPRODUCT((B32:B1000="MC")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000="*MC*")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000=*MC*)*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000,*MC*)*(C32:C1000="P1"))
>
> Any help would be greatly appreciated!!
>
>
> --
> altopalo
> ------------------------------------------------------------------------
> altopalo's Profile:
http://www.excelforum.com/member.php...o&userid=25276
> View this thread: http://www.excelforum.com/showthread...hreadid=387679
>

[Biff]

Hi!

Try this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("MC:",B32:B1000))),--(C32:C100="P1"))

Better:

Use cells to hold the criteria:

A1 = MC:
B1 = P1

=SUMPRODUCT(--(ISNUMBER(SEARCH(A1,B32:B1000))),--(C32:C100=B1))

Including the colon with the string helps reduce any false positives.

Biff

"altopalo" <[email protected]> wrote in
message news:[email protected]...
>
> Hi -
>
> I have a worksheet that has bug titles in column B and priority in
> column C.
>
> We have developed standard naming conventions for the bug title. For
> example:
>
> - MC: the Member Center has a bad help link in the header
> - FP: the Front Page has a broken image in the header
>
> I want to get a count of the bugs that contain a sub string of the
> title (e.g. "MC" or "FP") and also have a certain Priority level in
> column C.
>
> I've tried these without success:
>
> =SUMPRODUCT((B32:B1000="MC")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000="*MC*")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000=*MC*)*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000,*MC*)*(C32:C1000="P1"))
>
> Any help would be greatly appreciated!!
>
>
> --
> altopalo
> ------------------------------------------------------------------------
> altopalo's Profile:
> http://www.excelforum.com/member.php...o&userid=25276
> View this thread: http://www.excelforum.com/showthread...hreadid=387679
>

[Biff]

Hi!

Try this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("MC:",B32:B1000))),--(C32:C100="P1"))

Better:

Use cells to hold the criteria:

A1 = MC:
B1 = P1

=SUMPRODUCT(--(ISNUMBER(SEARCH(A1,B32:B1000))),--(C32:C100=B1))

Including the colon with the string helps reduce any false positives.

Biff

"altopalo" <[email protected]> wrote in
message news:[email protected]...
>
> Hi -
>
> I have a worksheet that has bug titles in column B and priority in
> column C.
>
> We have developed standard naming conventions for the bug title. For
> example:
>
> - MC: the Member Center has a bad help link in the header
> - FP: the Front Page has a broken image in the header
>
> I want to get a count of the bugs that contain a sub string of the
> title (e.g. "MC" or "FP") and also have a certain Priority level in
> column C.
>
> I've tried these without success:
>
> =SUMPRODUCT((B32:B1000="MC")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000="*MC*")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000=*MC*)*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000,*MC*)*(C32:C1000="P1"))
>
> Any help would be greatly appreciated!!
>
>
> --
> altopalo
> ------------------------------------------------------------------------
> altopalo's Profile:
> http://www.excelforum.com/member.php...o&userid=25276
> View this thread: http://www.excelforum.com/showthread...hreadid=387679
>

[RagDyer]

Try this:

=SUMPRODUCT((ISNUMBER(SEARCH("MC",B32:B1000)))*(C32:C1000="P1"))
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"altopalo" <[email protected]> wrote in
message news:[email protected]...
>
> Hi -
>
> I have a worksheet that has bug titles in column B and priority in
> column C.
>
> We have developed standard naming conventions for the bug title. For
> example:
>
> - MC: the Member Center has a bad help link in the header
> - FP: the Front Page has a broken image in the header
>
> I want to get a count of the bugs that contain a sub string of the
> title (e.g. "MC" or "FP") and also have a certain Priority level in
> column C.
>
> I've tried these without success:
>
> =SUMPRODUCT((B32:B1000="MC")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000="*MC*")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000=*MC*)*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000,*MC*)*(C32:C1000="P1"))
>
> Any help would be greatly appreciated!!
>
>
> --
> altopalo
> ------------------------------------------------------------------------
> altopalo's Profile:
http://www.excelforum.com/member.php...o&userid=25276
> View this thread: http://www.excelforum.com/showthread...hreadid=387679
>

[RagDyeR]

I guess the reason it worked beautifully was because it didn't contain a
typo, leaving out a "0".<vbg>

Appreciate the feed-back.

--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------


"altopalo" <[email protected]> wrote in
message news:[email protected]...

Thank you!!!!


=SUMPRODUCT((ISNUMBER(SEARCH("MC",B32:B1000)))*(C32:C1000="P1"))

worked beautifully


--
altopalo
------------------------------------------------------------------------
altopalo's Profile:
http://www.excelforum.com/member.php...o&userid=25276
View this thread: http://www.excelforum.com/showthread...hreadid=387679

[Biff]

Hi!

Try this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("MC:",B32:B1000))),--(C32:C100="P1"))

Better:

Use cells to hold the criteria:

A1 = MC:
B1 = P1

=SUMPRODUCT(--(ISNUMBER(SEARCH(A1,B32:B1000))),--(C32:C100=B1))

Including the colon with the string helps reduce any false positives.

Biff

"altopalo" <[email protected]> wrote in
message news:[email protected]...
>
> Hi -
>
> I have a worksheet that has bug titles in column B and priority in
> column C.
>
> We have developed standard naming conventions for the bug title. For
> example:
>
> - MC: the Member Center has a bad help link in the header
> - FP: the Front Page has a broken image in the header
>
> I want to get a count of the bugs that contain a sub string of the
> title (e.g. "MC" or "FP") and also have a certain Priority level in
> column C.
>
> I've tried these without success:
>
> =SUMPRODUCT((B32:B1000="MC")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000="*MC*")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000=*MC*)*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000,*MC*)*(C32:C1000="P1"))
>
> Any help would be greatly appreciated!!
>
>
> --
> altopalo
> ------------------------------------------------------------------------
> altopalo's Profile:
> http://www.excelforum.com/member.php...o&userid=25276
> View this thread: http://www.excelforum.com/showthread...hreadid=387679
>

[RagDyer]

Try this:

=SUMPRODUCT((ISNUMBER(SEARCH("MC",B32:B1000)))*(C32:C1000="P1"))
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"altopalo" <[email protected]> wrote in
message news:[email protected]...
>
> Hi -
>
> I have a worksheet that has bug titles in column B and priority in
> column C.
>
> We have developed standard naming conventions for the bug title. For
> example:
>
> - MC: the Member Center has a bad help link in the header
> - FP: the Front Page has a broken image in the header
>
> I want to get a count of the bugs that contain a sub string of the
> title (e.g. "MC" or "FP") and also have a certain Priority level in
> column C.
>
> I've tried these without success:
>
> =SUMPRODUCT((B32:B1000="MC")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000="*MC*")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000=*MC*)*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000,*MC*)*(C32:C1000="P1"))
>
> Any help would be greatly appreciated!!
>
>
> --
> altopalo
> ------------------------------------------------------------------------
> altopalo's Profile:
http://www.excelforum.com/member.php...o&userid=25276
> View this thread: http://www.excelforum.com/showthread...hreadid=387679
>

[RagDyeR]

I guess the reason it worked beautifully was because it didn't contain a
typo, leaving out a "0".<vbg>

Appreciate the feed-back.

--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------


"altopalo" <[email protected]> wrote in
message news:[email protected]...

Thank you!!!!


=SUMPRODUCT((ISNUMBER(SEARCH("MC",B32:B1000)))*(C32:C1000="P1"))

worked beautifully


--
altopalo
------------------------------------------------------------------------
altopalo's Profile:
http://www.excelforum.com/member.php...o&userid=25276
View this thread: http://www.excelforum.com/showthread...hreadid=387679

[RagDyeR]

I guess the reason it worked beautifully was because it didn't contain a
typo, leaving out a "0".<vbg>

Appreciate the feed-back.

--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------


"altopalo" <[email protected]> wrote in
message news:[email protected]...

Thank you!!!!


=SUMPRODUCT((ISNUMBER(SEARCH("MC",B32:B1000)))*(C32:C1000="P1"))

worked beautifully


--
altopalo
------------------------------------------------------------------------
altopalo's Profile:
http://www.excelforum.com/member.php...o&userid=25276
View this thread: http://www.excelforum.com/showthread...hreadid=387679

[RagDyer]

Try this:

=SUMPRODUCT((ISNUMBER(SEARCH("MC",B32:B1000)))*(C32:C1000="P1"))
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"altopalo" <[email protected]> wrote in
message news:[email protected]...
>
> Hi -
>
> I have a worksheet that has bug titles in column B and priority in
> column C.
>
> We have developed standard naming conventions for the bug title. For
> example:
>
> - MC: the Member Center has a bad help link in the header
> - FP: the Front Page has a broken image in the header
>
> I want to get a count of the bugs that contain a sub string of the
> title (e.g. "MC" or "FP") and also have a certain Priority level in
> column C.
>
> I've tried these without success:
>
> =SUMPRODUCT((B32:B1000="MC")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000="*MC*")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000=*MC*)*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000,*MC*)*(C32:C1000="P1"))
>
> Any help would be greatly appreciated!!
>
>
> --
> altopalo
> ------------------------------------------------------------------------
> altopalo's Profile:
http://www.excelforum.com/member.php...o&userid=25276
> View this thread: http://www.excelforum.com/showthread...hreadid=387679
>

[Biff]

Hi!

Try this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("MC:",B32:B1000))),--(C32:C100="P1"))

Better:

Use cells to hold the criteria:

A1 = MC:
B1 = P1

=SUMPRODUCT(--(ISNUMBER(SEARCH(A1,B32:B1000))),--(C32:C100=B1))

Including the colon with the string helps reduce any false positives.

Biff

"altopalo" <[email protected]> wrote in
message news:[email protected]...
>
> Hi -
>
> I have a worksheet that has bug titles in column B and priority in
> column C.
>
> We have developed standard naming conventions for the bug title. For
> example:
>
> - MC: the Member Center has a bad help link in the header
> - FP: the Front Page has a broken image in the header
>
> I want to get a count of the bugs that contain a sub string of the
> title (e.g. "MC" or "FP") and also have a certain Priority level in
> column C.
>
> I've tried these without success:
>
> =SUMPRODUCT((B32:B1000="MC")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000="*MC*")*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000=*MC*)*(C32:C1000="P1"))
> =SUMPRODUCT((B32:B1000,*MC*)*(C32:C1000="P1"))
>
> Any help would be greatly appreciated!!
>
>
> --
> altopalo
> ------------------------------------------------------------------------
> altopalo's Profile:
> http://www.excelforum.com/member.php...o&userid=25276
> View this thread: http://www.excelforum.com/showthread...hreadid=387679
>