# 2 separate columns with numbers separated by commas and single numbers not, need sum

1. ## 2 separate columns with numbers separated by commas and single numbers not, need sum

Good afternoon people. Thank you in advance. These forums are awesome and I appreciate it.

Have 1 sheet with 2 columns (columns D and F) have numbers in them, if more than 1 number, then commas are used to separate numbers, so 1 comma per 2 numbers, 2 commas per 3 numbers and so on.

In a separate column, (column I), need formula that counts the total amount of numbers and then multiplies the sum by 1200 for each row going down.

Example:

Row 7 has column D with (139, 123, 343) and column F with (134), in column I, essentially would be (4 numbers times 1200 equals 4800)

In other words each number, regardless of amounts in column D and F equal 1200 each equally.

- Nary

2. ## Re: 2 separate columns with numbers separated by commas and single numbers not, need sum

Hi and welcome to the forum

Assuming you are starting in row 1...
=(LEN(D1)-LEN(SUBSTITUTE(D1,",",""))+1+LEN(F1)-LEN(SUBSTITUTE(F1,",",""))+1)*1200

3. ## Re: 2 separate columns with numbers separated by commas and single numbers not, need sum

=((LEN(D7)-LEN(SUBSTITUTE(D7,",",""))+1)+(LEN(F7)-LEN(SUBSTITUTE(F7,",",""))+1))*1200

Regards, TMS

4. ## Re: 2 separate columns with numbers separated by commas and single numbers not, need sum

Hey Trevor, seeing as we both gave (pretty much) the same answer, would you call that "data validation"? lol

5. ## Re: 2 separate columns with numbers separated by commas and single numbers not, need sum

Thank you both for that. Excellent and most appreciated!!

6. ## Re: 2 separate columns with numbers separated by commas and single numbers not, need sum

@FD: absolutely: of course, I used the cell references in the OP example, but, other than that ... Great minds think alike

@Slothbob: thanks for the rep.

New quick method:

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

7. ## Re: 2 separate columns with numbers separated by commas and single numbers not, need sum

happy to help and thanks for the feedback

There are currently 1 users browsing this thread. (0 members and 1 guests)