Excel Unique & Difficult formula : Find date when target is achieved (Running total / Cumulative sum for item exceeding target)

Query:

I want a formula for searching date when a total target for particular agent exceeds between select dates. Basically, I have data sheet in which there is a date wise running sales of each agent, there is not a column of running total for each agent in it. I want to know for between any given dates, in a summary sheet for an agent on which date sales has exceeded a given target.

My database is maintained as below

I want a result in the last column from above date

Solution:

Since you are trying to extract result for not standard date range adding running total column will be of no use. Let's construct formula get the desired result:

Step -1 So first we will require to fetch a range which matches an agent name and which is within a date specified, 

=INDEX((Data!$B$2:$B$1000=B8)*(Data!$A$2:$A$1000>=C8)*(Data!$A$2:$A$1000<=D8)*ROW(Data!$C$2:$C$1000),,)

Above formula will give row number of meeting criterion we can get cell address by using formula

=(Data!$C$2:$C$1000)*( if (Step1_Formula>0,1,0) ),

so revised formula will be :

=(Data!$C$2:$C$1000)*(IF(INDEX((Data!$B$2:$B$1000=B8)*(Data!$A$2:$A$1000>=C8)*(Data!$A$2:$A$1000<=D8)*ROW(Data!$C$2:$C$1000),,)>0,1,0))

Here we are checking that if in step 1 formula if a value is zero (i.e. nonmatching item then keep row number to zero else keep this as 1, and multiply this array to data range to get the address of the matching value.

Step -2 Now since we have extracted the daily sales value of required criterion, then we will convert this daily sales value to cumulative sales value. E.G. we have extracted sales values as {100,100,0,0,100} but what we need to match is cumulative sales i.e. {100,200,200,200,300}.

To convert 1st array {100,100,0,0,100} to {100,200,200,200,300} we will use Matrix Multiplication formula, i.e. MMULT.

E.G. You have data in cell A1 to A5 as 1,2,3,4,5 and when you use MMULT formula as follows:

=MMULT(--(ROW(A1:A5)>=TRANSPOSE(ROW(A1:A5))),A1:A5)

Formula will return cumulative sum as {1,3,6,10,15}, i.e. cumulative sum is returned in array. Note MMULT is an array formula so after entering formula instead of pressing enter, use the combination of CSE keys (Control + Shift + Enter keys).

so our formula will look like as below:

=MMULT(--(ROW(Data!$E$2:$E$1000)>=TRANSPOSE(ROW(Data!$E$2:$E$1000))),(Data!$C$2:$C$1000)*(IF(INDEX((Data!$B$2:$B$1000=B8)*(Data!$A$2:$A$1000>=C8)*(Data!$A$2:$A$1000<=D8)*ROW(Data!$C$2:$C$1000),,)>0,1,0)))

Step -3 As we have received cumulative sum/total we can use LOOKUP formula & get resultant day by assigning array to it by using formula:

=LOOKUP(E8, Step2_Formula, Data!$A$2:$A$1000)+1

So final formula will look like this: 

{=LOOKUP(E8,MMULT(--(ROW(Data!$E$2:$E$1000)>=TRANSPOSE(ROW(Data!$E$2:$E$1000))),(Data!$C$2:$C$1000)*(IF(INDEX((Data!$B$2:$B$1000=B8)*(Data!$A$2:$A$1000>=C8)*(Data!$A$2:$A$1000<=D8)*ROW(Data!$C$2:$C$1000),,)>0,1,0))),Data!$A$2:$A$1000)+1}

Enter above formula as an array to get the desired result.

Download example sheet by clicking here.

Comment about this article & post your query below, also you can ask your query on www.ExcelVbaLab.Com

Cheers!!