0

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
0

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")
|improve this answer|||||
  • 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
0

sorry i miss read the question in my first answer.

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

|improve this answer|||||
  • 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
0

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))

Use this address in your INDIRECT:

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

or, if all cells in A1:D1 are filled,

=COUNTIFS(INDIRECT("E1:"&ADDRESS(COUNTA(E:E),COUNTA(1:1))),"Green")

Of course, you may use any another row and/or column for last cell detection.

|improve this answer|||||
  • Thanks Akina, I'm not quite sure which of the above to use as it I'm not familiar with the address and indirect functions. – Iza Bella Nov 14 '19 at 15:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.