Len

[Snit]

Does anyone know how to simplify the following eqution in Excel?

=SUM(LEN(C8)-LEN(SUBSTITUTE(C8,"D","")))/LEN("D")+SUM(LEN(C12)-LEN(SUBSTITUTE(C12,"D","")))/LEN("D")+SUM(LEN(C16)-LEN(SUBSTITUTE(C16,"D","")))/LEN("D")+SUM(LEN(C20)-LEN(SUBSTITUTE(C20,"D","")))/LEN("D")

Thanks for the help.

 

[T. Valko]

Try this:

=SUMPRODUCT(--(MOD(ROW(C8:C20),4)=0),LEN(C8:C20)-LEN(SUBSTITUTE(C8:C20,"D","")))

 

[Dave Peterson]

It doesn't look like you need any of the =sum()'s. Keep the ()'s, though.

And len("D") can be replaced by 1

And there doesn't seem to be much reason to divide by 1.

=(LEN(C8)-LEN(SUBSTITUTE(C8,"D","")))
+(LEN(C12)-LEN(SUBSTITUTE(C12,"D","")))
+(LEN(C16)-LEN(SUBSTITUTE(C16,"D","")))
+(LEN(C20)-LEN(SUBSTITUTE(C20,"D","")))

You really don't need the () around each addend.

=LEN(C8)-LEN(SUBSTITUTE(C8,"D",""))
+LEN(C12)-LEN(SUBSTITUTE(C12,"D",""))
+LEN(C16)-LEN(SUBSTITUTE(C16,"D",""))
+LEN(C20)-LEN(SUBSTITUTE(C20,"D",""))

And you could actually concatenate the values in the cell, too:

=LEN(C8&C12&C16&C20)-LEN(SUBSTITUTE(C8&C12&C16&C20,"D",""))

 

[Sandy Mann]

As writen the SUM() functions are not doing anything and LEN("D") is 1 so
your formula is actually:

=LEN(C8)-LEN(SUBSTITUTE(C8,"D",""))+LEN(C12)-LEN(SUBSTITUTE(C12,"D",""))+LEN(C16)-LEN(SUBSTITUTE(C16,"D",""))+LEN(C20)-LEN(SUBSTITUTE(C20,"D",""))

Or:

=SUM(LEN(C8)-LEN(SUBSTITUTE(C8,"D","")),LEN(C12)-LEN(SUBSTITUTE(C12,"D","")),LEN(C16)-LEN(SUBSTITUTE(C16,"D","")),LEN(C20)-LEN(SUBSTITUTE(C20,"D","")))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Snit]

Thanks guys for all of your suggestions. I tried T. Valko;2711118]Tr
this:

=SUMPRODUCT(--(MOD(ROW(C8:C20),4)=0),LEN(C8:C20)-LEN(SUBSTITUTE(C8:C20,"D",""))
and it worked out perfectly. All I had to do was change the endin
cell.

Thanks T. Valko and the rest of you.

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Thanks guys for all of your suggestions. I tried T. Valko;2711118]Try
this:

=SUMPRODUCT(--(MOD(ROW(C8:C20),4)=0),LEN(C8:C20)-LEN(SUBSTITUTE(C8:C20,"D","")))
and it worked out perfectly. All I had to do was change the ending
cell.

Thanks T. Valko and the rest of you.

Try this:

=SUMPRODUCT(--(MOD(ROW(C8:C20),4)=0),LEN(C8:C20)-LEN(SUBSTITUTE(C8:C20,"D","")))

--
Biff
Microsoft Excel MVP


"Snit" (e-mail address removed) wrote in message

Does anyone know how to simplify the following eqution in Excel?


=SUM(LEN(C8)-LEN(SUBSTITUTE(C8,"D","")))/LEN("D")+SUM(LEN(C12)-LEN(SUBSTITUTE(C12,"D","")))/LEN("D")+SUM(LEN(C16)-LEN(SUBSTITUTE(C16,"D","")))/LEN("D")+SUM(LEN(C20)-LEN(SUBSTITUTE(C20,"D","")))/LEN("D")

Thanks for the help.