I am trying to find a formula that replaces the numbers no matter what number it may be separated by a comma that will multiply by 4. That probably made no sense!

Here is an example of what I am trying to do:

say in Cell A1 I have 1,23,4,5

I would like it to CALCULATE in Cell A2 as totaling 16.so basically its adding 4 (regardless of what the number is followed by the comma. Its all increments of 4

say 1,2,3 =12

say 13,23,28,30 =16

say 2,6 =8

say 2 =4

basically the numbers are referring to dates such as May 1,23,30

so 1 representing 4 units 23 representing another 4 units 30 representing 4 units

therefor 4+4+4 = 12units

each day represents 1 hour of services which is equivalent to 4 units.

I hope that makes sense? If you can help me I greatly appreciate you! I have tried everything I can think of its probably a very simple formula and i am complicating the calculation!

Thank you :)

Cynn

Dear Mike,

THANK YOU THANK YOU THANK YOU!!! Thats it! I truly appreciate you and all your hard work! You Rock!!!!

Thanks again:))))

Cynn

Hello,

I was wondering if you might be able to help me with one more thing regarding this formula?

First, the formula works great!

My question is how would I get it to show in the formula column as a "-" or just left blank? Right now It always shows a 4 in that formula column and I would like for it show nothing until I start putting in the dates 1,2,23 etc.

I hope this made sense maybe?

Can you help me once again? I appreciate it and all your help always!

Thank you

Cynn

Hello,

I was wondering if you might be able to help me with one more thing regarding this formula?

First, the formula works great!

My question is how would I get it to show in the formula column as a "-" or just left blank? Right now It always shows a 4 in that formula column and I would like for it show nothing until I start putting in the dates 1,2,23 etc.

I hope this made sense maybe?

Can you help me once again? I appreciate it and all your help always!

Thank you

Cynn

Just wrap it in an IF() statement: =IF(A1="","",SUM((LEN(A1)-LEN(SUBSTITUTE(A1,",","")))*4)+4)

One other caveat you should be aware of:

The formula works by counting the number of commas in the string

so if you have a trailing comma, like:13,23,28,30, <-- trailing comma

you will get an incorrect answer.

MIKE

If the trailing comma is a concern then you could always do something like: =IF(A1="","",IF(RIGHT(A1,1)=",",SUM((LEN(A1)-LEN(SUBSTITUTE(A1,",","")))*4),SUM((LEN(A1)-LEN(SUBSTITUTE(A1,",","")))*4)+4))

MIKE

Once again you have solved my problem! I cant thank you enough! I truly appreciate all your time and effort put forth!!! You are amazing!

Thank you again!

Sincerely,

Cynthia

Ask Your Question

Weekly Poll

Do you think Microsoft can save the Surface Book lineup?

Discuss in The Lounge

Poll History