+ Reply to Thread
Results 1 to 6 of 6

Count cell range that doesn't contain specific strings or is empty

  1. #1
    Registered User
    Join Date
    10-10-2016
    Location
    Coimbra, Portugal
    MS-Off Ver
    2016
    Posts
    70

    Count cell range that doesn't contain specific strings or is empty

    Hi.
    I have this:

    =COUNTIF(A1:A5, {"<>String1","<>String2"})

    How do I add "empty"? (cell range that doesn't contain "String1", "String2" or is empty)

    PR

  2. #2
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Count cell range that doesn't contain specific strings or is empty

    Use CountIfs function. It allows two ranges and two criteria.
    Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, see the banner at top of this page.
    Just when I think I am smart, I learn something new!

  3. #3
    Registered User
    Join Date
    10-10-2016
    Location
    Coimbra, Portugal
    MS-Off Ver
    2016
    Posts
    70

    Re: Count cell range that doesn't contain specific strings or is empty

    =COUNTIFS(A1:A5, "<>String1", A1:A5, "<>String2", A1:A5, "<>")
    This works, thank you.

    Is it possible to do it as an array, though?
    COUNTIFS(A1:A5, {"<>String1", "<>String2", "<>"}) —> doesn't work

    PR

  4. #4
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Count cell range that doesn't contain specific strings or is empty

    maybe:
    =SUMPRODUCT((A1:A9<>"string1")*(A1:A9<>"string2")*(A1:A9<>""))

    edit:
    Oops, I maded sum instead of count
    Last edited by sandy666; 07-02-2018 at 12:59 PM.
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,125

    Re: Count cell range that doesn't contain specific strings or is empty

    Quote Originally Posted by Pr0 View Post
    =COUNTIFS(A1:A5, "<>String1", A1:A5, "<>String2", A1:A5, "<>")
    This works, thank you.

    Is it possible to do it as an array, though?
    COUNTIFS(A1:A5, {"<>String1", "<>String2", "<>"}) —> doesn't work

    PR
    COUNTIF(A1:A5,"<>"&"") works separately, but not in array format like this: COUNTIF(A1:A5,{...,...,"<>"&something})

  6. #6
    Registered User
    Join Date
    10-10-2016
    Location
    Coimbra, Portugal
    MS-Off Ver
    2016
    Posts
    70

    Re: Count cell range that doesn't contain specific strings or is empty

    Hmm, I'm not sure I understood that. Where do "String1" and "String2" go?

    PR

+ 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. Swap Specific Text Strings in a Range and Indicate new Strings Not already in Code.
    By liquidmettle in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-17-2018, 01:31 PM
  2. [SOLVED] VBA to locate an empty cell in specific range and add text
    By Sway2119 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-02-2017, 04:24 PM
  3. How to select next empty cell in row for a specific range?
    By 9mouse in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-15-2016, 08:28 AM
  4. [SOLVED] Find next empty cell (Row)within a specific range in column A
    By JRidge in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-26-2014, 05:39 AM
  5. [SOLVED] Select the last empty cell of the range specific
    By marreco in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-06-2013, 09:48 AM
  6. Macro to find next empty cell in specific range
    By boatbabe in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-06-2013, 09:18 AM
  7. Search for specific data and count number of rows till empty Cell
    By kjanani30 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-04-2013, 06:05 AM

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