# Excel - Indirect Formula

another question regarding Excel Indirect Formula.

I have columns E1:J202 filled with one of the following:

• Green
• Amber
• Red

How can I use the indirect formula to count each of the above separately if a new row or column is populated?

Note: Everytime I add a new column in excel (ie: if I insert a new column [K]), I want the system to count the Green, Amber and Red cells from F1:K202 instead of E1:J202.

• Does the rest of worksheet cells (below row 202 and to the right of column J) is empty? – Akina Nov 14 '19 at 11:58
• Correct Akina. The rows beneath 202 and columns next to J are empty (for now) – Iza Bella Nov 14 '19 at 12:13
• Is VBA function usage safe for you? – Akina Nov 14 '19 at 12:19
• Additionally - if you'll specify the whole worksheet in your counting formula then only used range of the worksheet will be processed. – Akina Nov 14 '19 at 12:26
• I would rather not use VBA for multiple reasons. – Iza Bella Nov 14 '19 at 12:30

Use INDEX:

``````=COUNTIFS(INDEX(1:202,1,MATCH(1E+99,1:1)-5):INDEX(1:202,202,MATCH(1E+99,1:1)),"green")
``````

It will find the last column with a true date and then create a range that includes it and 5 columns to the left.

If the "date' is actually text not a true date then use this:

``````=COUNTIFS(INDEX(1:202,1,MATCH("zzz",1:1)-5):INDEX(1:202,202,MATCH("zzz",1:1)),"green")
``````
• Its perfect for columns E:J, however I want to get the count from columns J:E but if another column is entered to the right (Column K), then I want the count from columns K:F. In other words, I need six months back from the last column that populated with data (in this case column K and so on). – Iza Bella Nov 14 '19 at 15:02
• Are the values in row 1 numbers/dates or text? – Scott Craner Nov 14 '19 at 15:04
• dates and text. So columns A:D are text and Columns E onwards are dates (starting with April 2019 (column E) to October 2019 (column K)) – Iza Bella Nov 14 '19 at 15:06
• Dates stored as strings or numbers formatted to look like dates? ie when you change the number format to general does a number appear or does the text remain the same. – Scott Craner Nov 14 '19 at 15:07
• number - the dates a formatted – Iza Bella Nov 14 '19 at 15:13

Try using the below formula:

``````=COUNTIFS(INDIRECT(ADDRESS(1,5)&":"&ADDRESS(202,(COLUMNS(\$A\$1:WZG1)-16221))),"green")
``````

this will break down if you add a column to the left of `column A` or to the right of `column WZF`

• Close! If a new column or row is inserted to the sheet, the formula counts the above criterias. However, If I add a new column, I want the formula to calculate the new column minus 5 rows before it. – Iza Bella Nov 14 '19 at 13:01

If the whole range cells are filled then use the below formula for to obtain the last cell in filled range:

``````=ADDRESS(COUNTA(E:E),COUNTA(1:1)-COUNTA(A1:D1)+4)
``````

If all cells in A1:D1 are filled too the formula may be

``````=ADDRESS(COUNTA(E:E),COUNTA(1:1))
``````

``````=COUNTIFS(INDIRECT("E1:"&ADDRESS(COUNTA(E:E),COUNTA(1:1)-COUNTA(A1:D1)+4)),"Green")
``````=COUNTIFS(INDIRECT("E1:"&ADDRESS(COUNTA(E:E),COUNTA(1:1))),"Green")