Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sumproduct based on substring?
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 |
#2
|
|||
|
|||
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" wrote in message ... 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 |
#3
|
|||
|
|||
Try this:
=SUMPRODUCT((ISNUMBER(SEARCH("MC",B32:B1000)))*(C3 2:C1000="P1")) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "altopalo" wrote in message ... 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 |
#4
|
|||
|
|||
Thank you!!!! =SUMPRODUCT((ISNUMBER(SEARCH("MC",B32:B1000)))*(C3 2: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 |
#5
|
|||
|
|||
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" wrote in message ... Thank you!!!! =SUMPRODUCT((ISNUMBER(SEARCH("MC",B32:B1000)))*(C3 2: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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct & indirect | Excel Worksheet Functions | |||
Sumproduct | Excel Worksheet Functions | |||
Can I reference =, <, or > sign in SUMPRODUCT | Excel Discussion (Misc queries) | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |