In J1, I want to count all occurrences of "x" between J4 and the bottom row of the worksheet.
I will have a varying number of rows. My kludge solution for that part of the problem is to populate column C with =ROW(), and then use =MAX(C4:C1048576) in C2 to get the number of rows in my range. At present, C2 reports 13841.
I get the end range specification for COUNTIF using =ADDRESS($C$2,COLUMN(),4) in J1. That gives me J13841. Then, since I seem to be incapable of understanding things like the INDIRECT function, I dress it up with some nice text, using ="=COUNTIF(J4:"&ADDRESS($C$2,COLUMN(),4)&","&CHAR(34)&"x"&CHAR(34)&")"
That gives me a display of the desired formula: =COUNTIF(J4:J13841,"x") although when I hit it with F2 I see that it's surrounded by curly brackets.
So now, if I ignore the curly braces, all that's missing is to convert that bit of manufactured text into an actual, working formula. I welcome suggestions on any part of this ... adventure.
Bookmarks