# too complex formula

1. ## too complex formula

I go around in a circle:
I have a row of headings in the range A1:NA1 where the headings are dates from 1 Jan to 31 Dec, i.e. 365 days
I want to use COUNTA to count a number of cells in a range that are not empty, i.e. in the row A2:NA2. This range includes a "P" in every cell below the corresponding date that a person was present.
However, whereas the start of the COUNTA range would be \$A\$2, I want the end of the range to correspond with the actual system date of the computer. For this I thought to use NOW.
But, whereas I can find the cell reference for NOW (in row 1), I would need something like OFFSET to find the relevant cell reference in row 2 for the COUNTA function to calculate. If I get this right, then every day the spreadsheet would tell me how many "P" days there were from 1 Jan to the present date - on every new day
And right here I get lost so much that when I turn around I walk into myself .
Can a good soul out there please point me in a direction?
Appreciated.

2. ## Re: too complex formula

Try

=COUNTA(OFFSET(A2,0,0,1,TODAY()-"1/1"+1))

3. ## Re: too complex formula

Thanks. It works well.

4. ## Re: too complex formula

Ah Jonmo1, one more thing if you'd be so kind please:
How would you write that formula in text?
I read A2 is the reference point, "0" because we are not moving up or down, next "0" means what?, "1" I assume to mean width and height, TODAY() for today's date, and what does -"1/1"+1 mean in the formula?
Thank you kindly.

5. ## Re: too complex formula

Originally Posted by Bab1
"0" because we are not moving up or down, next "0" means what?
Because we're not moving Left or Right either.
The ultimate range resulting from the Offset still begins in the originating cell A2.

Originally Posted by Bab1
"1" I assume to mean width and height
Actually, the 1 means Height only.

The "1/1" represents January First
The +1 means exactly what it looks like it means, Plus 1.

So TODAY()-"1/1"+1 is representing the Width of the range returned by offset.
And it translates as Today's Date Minus January 1st Plus 1
How many days from January first until Today's date.

Hope that helps.

There are currently 1 users browsing this thread. (0 members and 1 guests)