+ Reply to Thread
Results 1 to 5 of 5

Formula needed to only count total cells entered and not count adjacent text entered cells

  1. #1
    Forum Contributor
    Join Date
    07-06-2013
    Location
    USA
    MS-Off Ver
    MS Office 2019 Pro Plus
    Posts
    176

    Formula needed to only count total cells entered and not count adjacent text entered cells

    I want to count the cells with data entered and not count those that have a text entry in another cell.

    A1:A10 = entered numbers
    B1:B10 = entered text
    C1 = formula

    If i completely enter data in each of the 10 A cells, however in B1, B2, and B3 I enter "X", I want C1 to show 7 for the number of ells that have data not pertaining to "X"

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula needed to only count total cells entered and not count adjacent text entered c

    Like this...

    =COUNTIF(B1:B10,"<>X")

    EDIT: I think I misunderstood your requirement.

    Try this:

    =COUNTIFS(A1:A10,"<>",B1:B10,"<>X")
    Last edited by Tony Valko; 07-07-2013 at 05:33 PM.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Contributor
    Join Date
    12-23-2003
    Posts
    179

    Re: Formula needed to only count total cells entered and not count adjacent text entered c

    Try one of...

    =COUNTIFS(A1:A10,"<>",B1:B10,"<>X")

    =SUMPRODUCT(1-(A1:A10=""),1-(B1:B10="X"))

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,110

    Re: Formula needed to only count total cells entered and not count adjacent text entered c

    If B can contain text or numeric, try this...
    =COUNTIFS(A1:A10,">0",B1:B10,"<>")
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Forum Contributor
    Join Date
    07-06-2013
    Location
    USA
    MS-Off Ver
    MS Office 2019 Pro Plus
    Posts
    176

    Re: Formula needed to only count total cells entered and not count adjacent text entered c

    thank you it works

+ 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