+ Reply to Thread
Results 1 to 7 of 7

Excel 2007 : Cell references and COUNTIF help

  1. #1
    Registered User
    Join Date
    05-03-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    4

    Cell references and COUNTIF help

    Evening all, First time post, so I apologize if this is misplaced or not laid out properly.

    I am having issues referencing specific cells using both IF and COUNTIF....Maybe it'll be easier if I just post the fomulae and show you what is happening...

    I am using the following code to locate the last non-blank cell of a row, and the cell 8 before it
    copy to clipboard
    Please Login or Register  to view this content.
    Now both of those will return the desired results of $B$112 and $B$104. But when I try to use either the above codes as reference points in my logical statement, or when I use them in a separate cell and reference that cell, I get an error. Looks something like this:
    copy to clipboard
    Please Login or Register  to view this content.
    That will return a result of False, even though the cell that is being referenced (B112) does contain the text value of "Pal". When I manually enter the results ($B$112), the result comes back as True. I can't even get a single cell reference to work, much less use those in a range, which is part of the end goal. I am way out of my element here, so any assistance would be greatly appreciated.

  2. #2
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2007, 2013
    Posts
    6,522

    Re: Cell references and COUNTIF help

    Hi Marcus, welcome to the forum.

    The ADDRESS function returns a string representing the cell address, not the true cell reference. To reference the value in that cell you can use INDIRECT, e.g.

    =IF(INDIRECT(ADDRESS(LOOKUP(2,1/(B:B<>""),ROW(B:B)),2))="Pal",TRUE,FALSE)

    Also, True and False don't need to be in quotation marks. If they are, they return literal strings "True" and "False", not boolean values TRUE and FALSE (1 and 0).

  3. #3
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - Guimba-Nueva Ecija
    MS-Off Ver
    2010
    Posts
    3,796

    Re: Cell references and COUNTIF help

    maybe also like this.

    =IF(LOOKUP(2,1/(B:B<>""),B:B)="pal",TRUE,FALSE)
    "A man should look for what is, and not for what he thinks should be."
    Albert Einstein

    If you are satisfied click the Star to say Thank you.

    Regards,
    Vladimir

  4. #4
    Registered User
    Join Date
    05-03-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Cell references and COUNTIF help

    Paul, Worked great. Now I just need to figure out where the Sheet Reference goes in that formula. After that, I am one step away from having probably the most complicated part of my spreadsheet completed. Thanks again!

  5. #5
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2007, 2013
    Posts
    6,522

    Re: Cell references and COUNTIF help

    Sheet reference?

  6. #6
    Registered User
    Join Date
    05-03-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Cell references and COUNTIF help

    NVM, got it to work (mostly). Now it's just a matter of getting my logical statements correct. Here's the beast that I have constructed thus far, that your assistance helped create:

    copy to clipboard
    Please Login or Register  to view this content.
    Now the hiccup I am running into is that is is returning a true statement when I know it should not be. Probably some stupid minor thing that I am staring right at, but is eluding me. But since that is a different issue, I should make a new thread, no?

  7. #7
    Registered User
    Join Date
    05-03-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Cell references and COUNTIF help

    Okay, let me lay out the full story, since what I thought had been working before does not get me the expected results (probably due to my own ignorance, but here it goes)...

    Here's an example of my info, and EXACTLY what I am trying to do.

    copy to clipboard
    Please Login or Register  to view this content.
    This is essentially an attendance tracker that I am trying to automate some of the functions. What I am trying to accomplish is to craft a formula that returns a result of "Active" or "Inactive" depending on a rolling 8 week range. If the non "x" entries are greater than or equal to the x entries, then it should return the true result of Active. My last post has the full nested formula that I am using at current. The problem I am running into is that the formula always returns a result of Active. Upon breaking down the CountIFs into two separate formulae, regardless of the actual data in the specified range, I constantly get the result of 0 "x" entries and 8 non-"x" entries.

    When I break down the references without the INDIRECT, they generate the proper cells (last non-blank cell, and the same-7; $B$112 and $B$105 respectively). But when I use the INDIRECT to start off the LOOKUP, I get a result of zero. Is this normal, or am I totally missing something here?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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