The dataset showcases Sales Person , Target and units sold in Quarter-1.
To calculate how many months they took to achieve their Target:
Use the IFERROR, MATCH, SUBTOTAL, OFFSET, COLUMN, and MIN functions .
Step 1 – Applying a Combined Formula in the Output Cell
- Go to G5 and enter the formula.
=IFERROR(MATCH(TRUE,SUBTOTAL(9,OFFSET(D5,,,,COLUMN(D5:F5)-COLUMN(D5)+1))>=C5,0)-MIN(IF(D5:F5<>"",COLUMN(D5:F5)-COLUMN(D5)+1))+1,"Target Not Fulfilled")
As you enter the SUBTOTAL, you will see a list of function numbers. Choose 9 (to sum the numbers).
Formula Breakdown:
- COLUMN(D5) —> returns the column number of D5
- Output: {4}
- COLUMN(D5:F5) —> returns the column number of D5:F5
- Output: {4}, {5}, {6}
- IF(D5:F5<>””,COLUMN(D5:F5)-COLUMN(D5)+1) —> returns the output of the logical statement.
- IF({TRUE,TRUE,TRUE},{4}, {5}, {6}-{4}+1)
- Output: {1,2,3}
- IF({TRUE,TRUE,TRUE},{4}, {5}, {6}-{4}+1)
- MIN(IF(D5:F5<>””,COLUMN(D5:F5)-COLUMN(D5)+1)) —> determines the minimum value.
- MIN({1,2,3})
- Output: {1}
- MIN({1,2,3})
- OFFSET(D5,,,,COLUMN(D5:F5)-COLUMN(D5)+1) —> returns a reference to a range with specific height and width calculated by row down and column right.
- OFFSET(250,,,,{4}, {5}, {6}-{4}+1)
- Output: {250,250,250}
- OFFSET(250,,,,{4}, {5}, {6}-{4}+1)
- SUBTOTAL(9,OFFSET(D5,,,,COLUMN(D5:F5)-COLUMN(D5)+1))>=C5 —> returns the subtotal. Here, it will sum, as the function_num is 9.
- SUBTOTAL(9,{250,250,250})>=C5
- Output: {FALSE,TRUE,TRUE}
- SUBTOTAL(9,{250,250,250})>=C5
- MATCH(TRUE,SUBTOTAL(9,OFFSET(D5,,,,COLUMN(D5:F5)-COLUMN(D5)+1))>=C5,0)-MIN(IF(D5:F5<>””,COLUMN(D5:F5)-COLUMN(D5)+1))+1 —> returns the relative position of an item that matches a specific value.
- MATCH(TRUE, {FALSE,TRUE,TRUE},0-{1})+1
- Output: {2}
- MATCH(TRUE, {FALSE,TRUE,TRUE},0-{1})+1
- IFERROR(MATCH(TRUE,SUBTOTAL(9,OFFSET(D5,,,,COLUMN(D5:F5)-COLUMN(D5)+1))>=C5,0)-MIN(IF(D5:F5<>””,COLUMN(D5:F5)-COLUMN(D5)+1))+ 1,”Target Not Fulfilled”) —> finds whether the output is valid. If it isn’t, it will return “Target Not Fulfilled”
- =IFERROR({2}”Target Not Fulfilled”)
- Output: {2}
- =IFERROR({2}”Target Not Fulfilled”)
- Press ENTER.
This is the output.
Step 2 – Use the AutoFill Feature to Get the Count of Rows in the Table
- Drag down the Fill Handle to AutoFill the rest of the cells.
This formula calculates the Months Required to achieve the Target for each salesperson. Alex: 2 months.
Practice Workbook
Practice here.
Download Workbook
<< Go Back to Count Columns | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
