+ Reply to Thread
Results 1 to 7 of 7

Glitche in the Countif Function

  1. #1
    Registered User
    Join Date
    08-15-2006
    Posts
    71

    Glitche in the Countif Function

    Hi Guys,

    I'm experiencing a small problem with the Countif function. The criteria is as follows:

    A
    B
    C
    D

    However, the range in which the formula is searching for has cells that have data like the following:

    A
    B

    The above is in ONE cell and the Countif function doesn't counts the data within these cells. Does any one know how a work around for this?

    Thanks in advance for any help.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Not sure I understand or can picture it. Please elaborate on your data setup.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    08-15-2006
    Posts
    71
    hey,

    I've attached a word document that should give a good picture of what I mean. In excel, you can add in a line within a cell through "Alt+Enter", in the second line the data would be inputed in, hence the "A" at the top and the "B" at the bottom. Again, if I am confusing everyone, please see the attachment.

    Again, thanks in advace for any help
    Attached Files Attached Files

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Still not to sure what your intent is....

    but if you're looking for how many, say A's in the range...then perhaps

    =SUMPRODUCT(--ISNUMBER(FIND("A",F1:F4)))

    where F1:F4 is the range of data to look in, which may containing multiple letters in single cells.

  5. #5
    Registered User
    Join Date
    08-15-2006
    Posts
    71
    Thanks NVBC,

    Thats what I was looking for, but the forumla you gave me is just the tip of the ice berg. I need to incorporate this formual into the following formula:

    =SUMPRODUCT(('November Consolidation'!$L$9:$L$487=A12)*(ISNUMBER(SEARCH("Incomplete",'November Consolidation'!D$9:D$487))))

    In the above forumula, I am working in the "November Consolidation" worksheet, where I am searching in column L ($L$9:$L$487) that has the criteria in cell "A12" (the input data in cell A12 is "A"). Once the criteria is found in colomn L, the formual then references column D ($D$9:$D$487) to find "Incomplete" and match to the criteria that was found in column L, to tabulate the number of occurances.

    I need to some how incorporate the formual you gave me into the one above to specify in the above forumla to find the criteria in A12 within in column L even if the cells has multiple letters.

    I hope I haven't confused everyone.

    Again, thanks in advace for any help.

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Would it be?

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    08-15-2006
    Posts
    71
    I tried what you gave me, but it didn't work. I did however, tried the following:

    =SUMPRODUCT(--ISNUMBER(FIND("A",'November Consolidation'!$L$9:$L$487)))
    It did work, but I just can't seem to figure out how to incorporate the second half:

    *(ISNUMBER(SEARCH("Incomplete",'November Consolidation'!D$9:D$487)))
    Got any ideas?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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