Find and count (sumproduct)

  • Thread starter Thread starter James Kendall
  • Start date Start date
J

James Kendall

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))
 
James,

Try:

=SUMPRODUCT(ISNUMBER(FIND("OP",UPPER(MID('Jan-'!H2:H65536,1,6))))*('Jan-'!C2:C65536))

HTH,
Bernie
MS Excel MVP
 
sorry forgot the reference to another sheet

=SUMIF('Jan-'!H2:H65536,"*OP*",'Jan-'!C2:C65536)


Mike
 
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! 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 said:
James,

Try:

=SUMPRODUCT(ISNUMBER(FIND("OP",UPPER(MID('Jan-'!H2:H65536,1,6))))*('Jan-'!C2:C65536))

HTH,
Bernie
MS Excel MVP
Click to expand...
 
Back
Top