# Function to Recognize Text as Address in COUNTIF

1. ## Function to Recognize Text as Address in COUNTIF

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.

2. ## Re: Function to Recognize Text as Address in COUNTIF

do you have values in all cells in the range? or do you have blanks throughout?

if you have a column that has data on the last row (the following example is column A), you can get the last row number with this in cell C2:
``Please Login or Register  to view this content.``
although since you are just looking in column J, it could be:
``Please Login or Register  to view this content.``
then your countif formula can be done simply with this:
``Please Login or Register  to view this content.``

3. ## Re: Function to Recognize Text as Address in COUNTIF

Door No. 3 was the quick and simple answer for me. I could swear I tried that. Apparently I didn't have it quite right. Well, thank you for the help, and for the quick response. Cheers!

P.S. I should have asked, also, if there's a way to make the J variable. You know, have the formula detect which column it's in.

4. ## Re: Function to Recognize Text as Address in COUNTIF

This gets the column letter:
``Please Login or Register  to view this content.``
So, For the COUNTIF formula, that would be:
``Please Login or Register  to view this content.``
you could do similar to the SUMPRODUCT formula, but that means the "\$C\$2" will then be an unknown location, so that is something you can decide.

since you are simple looking for the number of "x" in the entire range, you dont even need to do the SUMPRODUCT, it is redundant.

just use:
``Please Login or Register  to view this content.``
and it will look in rows 4 to 1,000,000

By the way, thank you for the reps, much appreciated!

5. ## Re: Function to Recognize Text as Address in COUNTIF

Thanks again. You've helped me assemble a spreadsheet that may be helpful to some users. Can't post the link here now, because I'm too new; not clear when that will be allowed. Regardless, thank you and take care.

6. ## Re: Function to Recognize Text as Address in COUNTIF

hey raywood1,

You can upload a sample file by following the yellow banner at top.... but as for posting links, that should open up to you once you have made 10 posts/replies.

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