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