| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find and count (sumproduct)
I am trying to find OP in the list of cells. It works if it is the only
words in the cell but I need it to work if there is more than just "OP". Suggestions? =SUMPRODUCT(--(MID('Jan-'!H2:H65536,1,6)="OP"),+('Jan-'!C2:C65536)) -- Thank you for your time. Windows XP Office 2002 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find and count (sumproduct)
=SUMPRODUCT(--(ISNUMBER(SEARCH("OP",'Jan-'!H2:H65536))),'Jan-'!C2:C65536)
"James Kendall" wrote: I am trying to find OP in the list of cells. It works if it is the only words in the cell but I need it to work if there is more than just "OP". Suggestions? =SUMPRODUCT(--(MID('Jan-'!H2:H65536,1,6)="OP"),+('Jan-'!C2:C65536)) -- Thank you for your time. Windows XP Office 2002 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find and count (sumproduct)
James,
Try: =SUMPRODUCT(ISNUMBER(FIND("OP",UPPER(MID('Jan-'!H2:H65536,1,6))))*('Jan-'!C2:C65536)) HTH, Bernie MS Excel MVP "James Kendall" wrote in message ... I am trying to find OP in the list of cells. It works if it is the only words in the cell but I need it to work if there is more than just "OP". Suggestions? =SUMPRODUCT(--(MID('Jan-'!H2:H65536,1,6)="OP"),+('Jan-'!C2:C65536)) -- Thank you for your time. Windows XP Office 2002 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find and count (sumproduct)
James,
Maybe =SUMIF(H2:H65536,"*OP*",C2:C65536) Mike "James Kendall" wrote: I am trying to find OP in the list of cells. It works if it is the only words in the cell but I need it to work if there is more than just "OP". Suggestions? =SUMPRODUCT(--(MID('Jan-'!H2:H65536,1,6)="OP"),+('Jan-'!C2:C65536)) -- Thank you for your time. Windows XP Office 2002 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find and count (sumproduct)
sorry forgot the reference to another sheet
=SUMIF('Jan-'!H2:H65536,"*OP*",'Jan-'!C2:C65536) Mike "James Kendall" wrote: I am trying to find OP in the list of cells. It works if it is the only words in the cell but I need it to work if there is more than just "OP". Suggestions? =SUMPRODUCT(--(MID('Jan-'!H2:H65536,1,6)="OP"),+('Jan-'!C2:C65536)) -- Thank you for your time. Windows XP Office 2002 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find and count (sumproduct)
This one works best for the functionality.
It allows any length of text which if the operator hits an extra keystroke would throw off my other criteria of 6 positions. Thanks! -- Thank you for your time. Windows XP Office 2002 "Teethless mama" wrote: =SUMPRODUCT(--(ISNUMBER(SEARCH("OP",'Jan-'!H2:H65536))),'Jan-'!C2:C65536) "James Kendall" wrote: I am trying to find OP in the list of cells. It works if it is the only words in the cell but I need it to work if there is more than just "OP". Suggestions? =SUMPRODUCT(--(MID('Jan-'!H2:H65536,1,6)="OP"),+('Jan-'!C2:C65536)) -- Thank you for your time. Windows XP Office 2002 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find and count (sumproduct)
Thank you! This works. If I was sure the operators would not accidentally
key more than 6 characters I would use this one. Thanks for the quick response! -- Thank you for your time. Windows XP Office 2002 "Bernie Deitrick" wrote: James, Try: =SUMPRODUCT(ISNUMBER(FIND("OP",UPPER(MID('Jan-'!H2:H65536,1,6))))*('Jan-'!C2:C65536)) HTH, Bernie MS Excel MVP "James Kendall" wrote in message ... I am trying to find OP in the list of cells. It works if it is the only words in the cell but I need it to work if there is more than just "OP". Suggestions? =SUMPRODUCT(--(MID('Jan-'!H2:H65536,1,6)="OP"),+('Jan-'!C2:C65536)) -- Thank you for your time. Windows XP Office 2002 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find and count (sumproduct)
Sorry, this one did not work.
Thanks for the try. -- Thank you for your time. Windows XP Office 2002 "Mike H" wrote: James, Maybe =SUMIF(H2:H65536,"*OP*",C2:C65536) Mike "James Kendall" wrote: I am trying to find OP in the list of cells. It works if it is the only words in the cell but I need it to work if there is more than just "OP". Suggestions? =SUMPRODUCT(--(MID('Jan-'!H2:H65536,1,6)="OP"),+('Jan-'!C2:C65536)) -- Thank you for your time. Windows XP Office 2002 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find and count (sumproduct)
Great! This work also!
I like it because it is shorter. Thanks! -- Thank you for your time. Windows XP Office 2002 "Mike H" wrote: sorry forgot the reference to another sheet =SUMIF('Jan-'!H2:H65536,"*OP*",'Jan-'!C2:C65536) Mike "James Kendall" wrote: I am trying to find OP in the list of cells. It works if it is the only words in the cell but I need it to work if there is more than just "OP". Suggestions? =SUMPRODUCT(--(MID('Jan-'!H2:H65536,1,6)="OP"),+('Jan-'!C2:C65536)) -- Thank you for your time. Windows XP Office 2002 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMPRODUCT, count & sum | Excel Worksheet Functions | |||
| count if or sumproduct? | Excel Discussion (Misc queries) | |||
| Count without SUMPRODUCT | Excel Worksheet Functions | |||
| Count if and Sumproduct | Excel Discussion (Misc queries) | |||
| How do i count numbers and letters to find a total count of all | Excel Worksheet Functions | |||
Linear Mode
