Help with an Excel formula (numerical array)

[highspeedlane]

I'm looking for a way for a formula to multiply the value of a cell based on the count of the numbers within it.

For example, the formula now takes the dollar value in one cell X quantity of another cell (i.e, $1200.00 x 2) to produce the result in a third cell. But within another cell is the applicable number of quarters in a year that money has to be spent.  They are displayed as numbers separated by commas; for instance, 1,3,4. Those are the quarters of the year in which a specific amount of money will be spent. I need that cell to count as a number 3 for multiplication purposes so the total output to another cell represents $1200 X quantity X number of quarters in the year (in this example the output should total $7200 ($1200 X 2 X 3)

Attached is a screenshot example. The current formula accounts for the value of D14 X E14 (output in F14) but does not factor in G14, which should represent a multiplier of 2 (that output would go in K14.

Does anyone know what the formula would be in order to produce that result? I am using Excel 2016 if that helps. Thank you very much and greatly appreciate any assistance. Good day all.

[#2 · Accepted solution]

Hi,

If I understood correctly the task is to calculate the number of digits in the text where could be from 1 to 4 digits separated by commas, like this

That could be classical formula (column B)

=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1

or for your case bit shorter one (column C)

=INT(LEN(A1)/2)+1

and attached

[#3]

Yes, that was exactly what I was looking for. Thank you!

[#4]

You are welcome