yogi_Compute Row By Row Project Number Starting From Row 3 Down

                Google Spreadsheet   Post  #1837

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov-19-2014

post by Tommy Coute:
(https://productforums.google.com/forum/#!mydiscussions/docs/6hYr0QiEE3Q)
How can I use an ArrayFormula to count populated cells within a dynamic range?

Hi there,

I am having trouble creating a formula that will do the following for me:

1. Count the populated cells in row A from the current row to row 3.
2. Keep doing this correctly even if I move rows around.
3. Keep doing this correctly even if I add or remove rows.

I found I can accomplish the first two requirements with the formula:

=counta(indirect("a"&row()&":a3"))

I tried using an ArrayFormula to automatically and persistently populate an entire column, which I have since learned will not work using the "indirect" function. Here is what I tried anyway (using an IF statement was the only way I could find to get the ArrayFormula to extend the range to other cells): 

=arrayformula(if(A3:A<>"",counta(indirect("A"&row()&":A3")),counta(indirect("A"&row()&":A3"))))

It seems there is probably a much simpler way to do this that I am missing.  I am just trying to use "counta" for a single column and display a value for the number of populated cells above a given row.

I have shared an example here: https://docs.google.com/spreadsheets/d/1m9YipKeNv_-qUgqM0E7AqoCzBnYzFfSTbB-HbREWAGk/edit?usp=sharing

p.s. for anyone wondering why I'm trying to do this... I'm trying to implement some cool conditional formatting that will alternate row colors based on the presence of data in the first column.  This works by numbering the "project" references (which I am trying to automate here!), and then toggling the formatting for even/odd values

---

Hi Yogi, thanks for the reply!

I am trying to accomplish what is suggested (but not entirely achieved) by column I of the linked spreadsheet.  That is to say, I want the value shown in each cell of column I to display the number of non-empty cells from A3 to A(ROW()).  For example: I8 displays "2", which is good because COUNTA(A8:A3) = 2.

This is accomplished by pasting the formula "=COUNTA(INDIRECT("A"&ROW()&":A3"))" into every cell from I3:I, and therein lies the problem.  I was hoping I would not have to manually paste the formula into each cell, but rather use an ArrayFormula or some other method to automatically populate I3:I for any rows that are added in the future.

If you check cell J3 you will see my failed attempt to use this same formula within an ArrayFormula, but I don't believe I can do this using "INDIRECT".

Let me know if that explains my situation, thanks!

----------------------------------------------------------------------------------------------------------------------------------