+ 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
    250

    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
    Ipswich, England
    MS-Off Ver
    Excel 2019 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    32,618

    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!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    1,834

    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
    Valued Forum Contributor XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,175

    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
    250

    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)

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