How do i make this formula not case sensitive?

so im using this formula to return a value based on the letter U in a cell:

=SUM(LEN(BO5:BO14)-LEN(SUBSTITUTE(BO5:BO14;"U";"")))

but how do i make it not case sensitive, as it stands now, it only registers capitals, and its the other way around, if i make the u small in the formula, can you come around this?

thanks

If you use this variation, you won't need to use CTRL+SHIFT+ENTER to make it an array formula.
Regular formula:
=SUMPRODUCT(LEN(BO5:BO14)-LEN(SUBSTITUTE(UPPER(BO5:BO14),"U","")))

Is that something you can work with?

HI Ron,

Forgive my ignorance, but I have read that SumProduct is volatile (others have said its not). Therefore is there a difference between SumProduct and Array formulas since both are volatile.

Thanks in advance for your insight.

SUMPRODUCT is not volatile.

See Charles Williams' site:
http://www.decisionmodels.com/calcsecretsi.htm

Hi Ron,

Thanks for the confirmation. Half the posts I read that Sumproduct is volatile and the other half say its not. Sometimes I'm not sure who to believe. I tend to stay away from it anyways because I rarely work on Excel 03 or before so it's not a big issue for me, but I know this comes up alot when people don't have the newer versions of Office available to them.

Originally Posted by abousetta

Hi Ron,

I tend to stay away from it anyways....

WHY??? In my Excel classes I spend 30 minutes covering "SUMPRODUCT, Excel's most versatile function"!

I guess I never got the hang of it, but also because of suggestions like this from MSDN (http://msdn.microsoft.com/en-us/library/ff726673.aspx):

"Starting in Excel 2007, you should always use the SUMIFS, COUNTIFS, and AVERAGEIFS functions instead of SUMPRODUCT formulas where possible."

That's different from "I tend to stay away from it".

In those circumstances where the SUMIFS, COUNTIFS, and AVERAGEIFS functions apply, absolutely use them.
SUMPRODUCT nicely covers almost all of the other instances where, for one reason or another, those don't work.

Hi Ron,

Agree. When I stated "I tend to stay away from it", I didn't mean to imply that the reason was because of an inherent issue with SumProduct but rather that I have not really found a need for it because other functions available since 2007 sufficed my needs. I see some people use instead of sumifs, etc. even with Excel 2010 because it is backwards compatible with 03 and earlier versions of Excel, which is not a priority for my needs.

I DID modify your original formula...
I used the UPPER function to force the text to be uppercase and I replaced SUM with SUMPRODUCT (eliminating the need to CSE the formula).

Perhaps you could do this...
• Find/Replace all: = and replace with: |=....that will convert all formulas to text
• Find/Replace all: SUBSTITUTE( and replace with: SUBSTITUTE(UPPER(
• Find/Replace all: ;"U" and replace with: );"U"
• Find/Replace all: |=SUM and replace with: =SUMPRODUCT

That would convert all of your formulas to the new version.

Is that something you can work with?

Hi Ron,

You made me research the SumProduct Volatile or NOT question. See http://spreadsheet-toolbox.com/libra...in-excel-2007/ which implies it might be in some versions and/or with a uniary operator in it.

I have no way of checking for sure. I'll keep learning...

@MarvinP: I'm pretty sure that, if you have a volatile function in your workbook, you'd get the "do you want to save the changes you made ..." message if you simply open the workbook, do nothing to it and then close it again.

An easy test is with INDIRECT: if you put "C1", no quotes, in cell A1, =INDIRECT(A1) in cell B1, and 5 (or whatever) in cell C1, the formula will return 5 (or whatever you put in it) in cell B1.

Now, if you save and close this new workbook with nothing except those three cells, open it again ... do nothing, make no changes ... and close it again, you'll get the message above. So, that's volatile for you.

Try it again with (any) SumProduct ... no message ... not volatile.

Tested in 2003 and 2007.

Regards, TMS

Great answer and clear, to me at least.

@MarvinP: you're welcome.

@icestationzbra: thanks for the rep. Yep, always confused me. Easy one to spot is =TODAY()