+ 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 (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,401

    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.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    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,704

    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. [SOLVED] 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