# How to show which column has the first amount and count columns to reach a sum amount

I am working on a Google sheet that shows a sum that has been advanced to a customer and then shows the income per month the customer receives. We will retain 100% of the income until the amount that has been advanced is repaid.

I am trying to come up with two formulas for this spreadsheet which will work on each row:

1. in column C that returns the first month that revenue is earned by the client and
2. in column D that returns the number of months over which the client revenues pay back the advance listed in column B.

My research suggests that this is probably possible using an array function but I can't seem to find an example and don't understand the syntax enough to make it work.

I would suggest using some helper columns for the second part.

This said, for the first part, I propose this formula:

``````=index(\$E\$2:\$P\$2,match(false,isblank(E3:P3),0))
``````

For the second part, I propose this formula (note it has dependents on the helper columns)

``````=datedif(C3,index(\$Q\$2:\$AB\$2,match(true,Q3:X3>=B3,0)),"M")+1
``````

The helper columns only have running totals (adds up everything up to but excluding the current month).

Datedif finds the difference between two dates, and in the above, will return that difference in months.

EDIT: Added the formula for running totals for the second part, which works without the helper columns:

``````=DATEDIF(C3,index(\$E\$2:\$P\$2,match(true,MMULT((column(E3:P3)<=transpose(column(E3:P3)))*E3:P3,transpose(SIGN(E3:P3)))>=B3,0)),"M")+1
``````

This bit:

``````MMULT((column(E3:P3)<=transpose(column(E3:P3)))*E3:P3,transpose(SIGN(E3:P3)))
``````

Returns an array containing the running totals, referenced from this website and adapted to a row (the website uses a column of values and explains in fairly good detail how it works).

• That's great - many thanks. The first part is perfect exactly what I am after. In the second part is there a way of doing this without the helper columns? It will eventually be quite a complex sheet and the helper columns double the size May 3, 2018 at 13:41
• @KentonWard Yes, it's possible, but much more complex and I often have to look it up myself. Will update my answer in a bit then. May 3, 2018 at 13:48
• @KentonWard Updated. May 3, 2018 at 14:14