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

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

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

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

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

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

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

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

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