Len
 

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.




--
Snit

Len
 

Try this:

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

--
Biff
Microsoft Excel MVP


"Snit" 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.




--
Snit

Len
 

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



Snit wrote:

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.

--
Snit

--

Dave Peterson

Len
 

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


Replace @mailinator.com with @tiscali.co.uk


"Snit" 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.




--
Snit

Len
 

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.


T. Valko;2711118 Wrote:
Try this:

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

--
Biff
Microsoft Excel MVP


"Snit" 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.




--
Snit -




--
Snit

Len
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Snit" wrote in message
...

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.


T. Valko;2711118 Wrote:
Try this:

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

--
Biff
Microsoft Excel MVP


"Snit" 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.




--
Snit -




--
Snit