+ Reply to Thread
Results 1 to 6 of 6

Function to Recognize Text as Address in COUNTIF

  1. #1
    Registered User
    Join Date
    05-03-2022
    Location
    Texas
    MS-Off Ver
    2016
    Posts
    10

    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. #2
    Valued Forum Contributor
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    856

    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.
    Last edited by janmorris; 05-03-2022 at 06:12 PM.
    As a gesture off appreciation, you can click * Add Reputation at the foot of any of the posts of members who helped you reach a solution.

    And finally, was your problem solved? if so, please click Thread Tools above the first post of your enquiry, then select [Solved]

  3. #3
    Registered User
    Join Date
    05-03-2022
    Location
    Texas
    MS-Off Ver
    2016
    Posts
    10

    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.
    Last edited by raywood1; 05-03-2022 at 10:18 PM.

  4. #4
    Valued Forum Contributor
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    856

    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!
    Last edited by janmorris; 05-04-2022 at 01:47 AM.

  5. #5
    Registered User
    Join Date
    05-03-2022
    Location
    Texas
    MS-Off Ver
    2016
    Posts
    10

    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. #6
    Valued Forum Contributor
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    856

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. IF function to recognize text and/number?
    By excelforyou in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-13-2015, 06:10 PM
  2. countif does not recognize < and > symbols in excel cell values
    By chengg02 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 07-22-2013, 02:40 PM
  3. How to make a function recognize a text in the function?
    By Ericng in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 05-30-2013, 05:25 AM
  4. [SOLVED] Why does Excel not recognize email address?
    By Pat in forum Excel General
    Replies: 7
    Last Post: 04-15-2009, 01:31 PM
  5. Sumif function won't recognize text
    By roasthawg in forum Excel General
    Replies: 2
    Last Post: 11-02-2008, 03:14 PM
  6. Some cells do not recognize email address
    By Coachstan in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 04-24-2005, 09:06 PM
  7. Replies: 0
    Last Post: 02-05-2005, 10:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1