+ Reply to Thread
Results 1 to 7 of 7

counting text in a column w/text criteria to other column

  1. #1
    LnghairdFreak
    Guest

    counting text in a column w/text criteria to other column

    Hi,
    I have checked the FAQ's and searched the threads to no avail. Basically I need these two formulas.
    COUNTA(range)if COUNTA(range="text") and COUNTBLANK(range)if COUNTA(range="text")
    Counting the number of cells in a column that contain text related to text entries in another column and counting the blank cells in a column relating to text entries in another column. It sounds simple but I cannot find a function or array combination that works. The entries I refer to change daily and there are thousands of them. I currently do it by hand, (inserting rows and resorting and changing the range in the formula bar) This is VERY time consuming. I would like to be able to just add a new entry anywhere in the list and let the formula do the work.

    Thanks in advance

  2. #2
    Registered User
    Join Date
    07-27-2006
    MS-Off Ver
    MS Office 2007
    Posts
    79
    Hi,
    could you attach an example of your spreadsheet with some typical inputs along with required output? It would help in understanding what you need...
    TL

    https://sites.google.com/site/teelim/
    My page of "not so useful" spreadsheets

  3. #3
    LnghairdFreak
    Guest
    Teelim,

    Sure, here is one of them. What I want is if you look at the DATA sheet in the wrkbk for example:
    Column D counts entries marked as complete(containing text) Column E counts blank cells(incomplete). Thes results are then shown on the SYNOPSIS Tab. What I want is the same result but instead of changing the range(by hand) if there is an addition/subtraction to column I it would find it and change the result.
    eg: If I add an entry to row 832 column I on the DATA sheet where cell I832 contains NE1, I need to change the formula range in columns D & E after sorting columns H through S. I want the formula to "look for the specific text NE1" in column I and then count the blank/non-blank cells in the corresponding columns K to S. My original post actually contains the "WORDING" for what I need, just not in a workable formula. Hope this and/or the file will enable you to help me.
    Attached Files Attached Files

  4. #4
    LnghairdFreak
    Guest

    No Answer

    Can't be Done??
    Hmmm, evidently this is a stumper or it is just not possible. Maybe I have to re-look at how the spreadsheet is structured?

  5. #5
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Yes, it can be done, use this formula in data!D3

    =SUMPRODUCT((I$2:I$2000=A3)*(K$2:S$2000<>""))

    copy down column

    use the same formula in E3 but replace <> with =

    Note: you need to change A3 to NE 1 [with a space] to match the entries in column K.

    I picked 2000 rows, you should pick a number of rows which will accommodate future additions to your data, use 50000 if yu have to but it may slow the calculations down, you can't use whole column references with SUMPRODUCT

  6. #6
    LnghairdFreak
    Guest

    daddylonglegs

    What am I missing?

    That formula returns 0. Something needs to be put in the second set of parenthensis (K:S). I cannot put anything in there that is a constant. I need to count either BLANK or NON BLANK cells.

  7. #7
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    It works for me, gives me the same answer as yours

    Have you changed A3 as I said? that needs to be NE 1, so that it matches your entries in column K

    =SUMPRODUCT((I$2:I$2000=A3)*(K$2:S$2000<>""))

    The second part, K$2:S$2000<>"", means K2:S2000 is not blank.

    If you still can't make it work, PM me your email address and I'll send you a version of your spreadsheet with the formula(s) working

+ 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