+ Reply to Thread
Results 1 to 5 of 5

How to counti cells that are not blank but ignoring formulas

  1. #1
    Forum Contributor
    Join Date
    09-17-2009
    Location
    Torquay, England
    MS-Off Ver
    Excel 2013
    Posts
    253

    How to counti cells that are not blank but ignoring formulas

    Hi,

    I would like to use countifs to count all not blank entries in column A that are also not blank in column B. Column B contains formulas, so I want to avoid counting blanks cells that have a formula in them.

    I'm comfortable using countifs, but basically I can't find the syntax to say [countif not blank (unless a blank cell has a formula, in which case count that as blank)]

    Many thanks for any help

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,054

    Re: How to counti cells that are not blank but ignoring formulas

    If formulae are returning blanks properly, then there should be no issue.

    Attach a sample workbook for troubleshooting, please.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: How to counti cells that are not blank but ignoring formulas

    Should be like this: =SUMPRODUCT(--($A$1:$A$12=$B$1:$B$12))
    Click the * to say thanks.

  4. #4
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,706

    Re: How to counti cells that are not blank but ignoring formulas

    in terms of COUNTIFS, specifically, it all rather depends on what B is returning -- if text

    =COUNTIFS($A$1:$A$100,"",$B$1:$B$100,"?*)

    the use of ? will account for any null strings generated by formulae.

    if you're returning numbers in Col B use a number based test, e.g.:

    =COUNTIFS($A$1:$A$100,"",$B$1:$B$100,">-9.99E+307")

    if you need to account for both data types you would need to combine the 2 COUNTIFS, e.g.

    =SUM(COUNTIFS($A$1:$A$100,"",$B$1:$B$100,{"?*",">-9.99E+307"}))

    edit: I misread your criteria re: Column A, i.e. non-blank constants as opposed blank constants, but I suspect you can amend the above examples to account for your real-life requirements.
    Last edited by XLent; 11-01-2019 at 10:39 AM.

  5. #5
    Forum Contributor
    Join Date
    09-17-2009
    Location
    Torquay, England
    MS-Off Ver
    Excel 2013
    Posts
    253

    Re: How to counti cells that are not blank but ignoring formulas

    Thanks all, each was helpful - I managed to use Countifs, I just had the syntax incorrect. Thank you for your feedback.

+ 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. [SOLVED] Ignoring Blank Cells in Formulas??
    By R. Turner in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-08-2017, 08:43 AM
  2. Replies: 3
    Last Post: 07-24-2017, 01:41 PM
  3. [SOLVED] Formulas within IFs. In a col, ignoring blank cells above until you reach a number then +1
    By MushroomFace in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-24-2014, 06:45 PM
  4. how to average large formulas ignoring 0 and blank
    By nsmjc in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 06-23-2014, 03:10 PM
  5. Dynamic Ranges - Ignoring Blank Cells with Underlying Formulas
    By jmm722 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-25-2014, 04:14 PM
  6. [SOLVED] Data validation using dynamic range ignoring blank formulas
    By gerainta in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-22-2013, 09:24 AM
  7. Ignoring Blank/Empty Cells that contain formulas
    By pabown in forum Excel General
    Replies: 4
    Last Post: 01-25-2005, 06:06 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