+ Reply to Thread
Results 1 to 11 of 11

COUNTIFS with blank cells

  1. #1
    Registered User
    Join Date
    03-20-2021
    Location
    Galway, Ireland
    MS-Off Ver
    MS Office 2007
    Posts
    5

    COUNTIFS with blank cells

    Hi all,

    I am looking for a little help with the COUNTIFS formula.
    I have the COUNTIFS working for a defined range but would like to expand this for a full column.
    As the column now contains blank cells, I get the dreaded #VALUE! response.
    I have used the below formula for the Supplier 1 result.
    =COUNTIFS(A5:A14;"Supplier 1";B5:B14;"Supplier")

    The attached file shows an example of an Issue Log & Issue Tracker.
    The Issue Tracker should hopefully only display the number of issues defined as Supplier on the Log.

    Any help with this would be appreciated.


    Thanks,
    Niall
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: COUNTIFS with blank cells

    Hi & welcome to the board.
    This work for me in G5 copied down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: COUNTIFS with blank cells

    Hi and welcome, Niall.

    Change the formula in G5 to the one below and copy down as required.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    BSB

  4. #4
    Registered User
    Join Date
    03-20-2021
    Location
    Galway, Ireland
    MS-Off Ver
    MS Office 2007
    Posts
    5

    Re: COUNTIFS with blank cells

    Hi,

    Thanks for the response, unfortunately when I try =COUNTIFS(A:A,F5,B:B,"Supplier") I am getting an error message.
    Would this be due to my older version of excel?

    Also is there any that I could add to my previous formula that will take the full range of column A but only process the cells that contain data and disregard any blank cells, as they will be populated in time.

  5. #5
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: COUNTIFS with blank cells

    The error message is down to regional settings. Switch the commas for semicolons and that should solve it.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Does referencing the entire columns as suggested above cause any slow down in your workbook?
    If so you could use dynamic named ranges that expand/contract as data is added/removed. But the speed saving there wouldn't necessarily be noticable and would potentially only cause more headache than you need.

    BSB

  6. #6
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,807

    Re: COUNTIFS with blank cells

    I believe COUNTIFS did not come out until V2019. Since the OP is on V2007, I don't believe COUNTIFS will work.

    Will this work in your version? (in G5 and copy down):

    =SUMPRODUCT((A5:A14=F5)*(B5:B14="Supplier"))

    (or this)
    =SUMPRODUCT(--(A5:A14=F5),--(B5:B14="Supplier"))

  7. #7
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,807

    Re: COUNTIFS with blank cells

    I'm sorry, I see in the initial post the OP says that he's using COUNTIFS (and it's in the SUBJECT line!!) I'm not sure how that's possible with V2007, but I guess it is.

  8. #8
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: COUNTIFS with blank cells

    Quote Originally Posted by Gregb11 View Post
    I believe COUNTIFS did not come out until V2019. Since the OP is on V2007, I don't believe COUNTIFS will work.
    Sorry @Gregb11, even if this were true (it's not as COUNTIFS is available in all versions of Excel from 2007 onward) your proposed SUMPRODUCT solution still wouldn't do what the OP is (presumably) trying to do as the formula would still need adjusting every time new rows of data were added.

    @NiallC92 - The COUNTIFS solution proposed in posts #2 and #3 above will work, albeit with the slight change of commas being changed to semicolons. See the yellow cells in the attached version of your file.
    I'd hazard a guess that you got the #VALUE! error if you only had one of the ranges in the formula set to the entire column. For example, if it looked something like this =COUNTIFS(A:A,F5,B5:B14,"Supplier") then you would get a #VALUE! error as it requires both ranges to be the same length.

    BSB
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    03-20-2021
    Location
    Galway, Ireland
    MS-Off Ver
    MS Office 2007
    Posts
    5
    Thanks for all the help.
    The formula is now operating as I had hoped.
    Changing to semicolons and defining both ranges as the full columns was what I was missing.

  10. #10
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: COUNTIFS with blank cells

    Glad we could help

    BSB

  11. #11
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,807

    Re: COUNTIFS with blank cells

    Yeah, @BadlySpelledBuoy, I was just going by the Microsoft support site:

    https://support.microsoft.com/en-us/...n-us&ad=us#bm3

    Where it shows COUNTIFS didn't come out till 2019. Then at their detail site for countifs:
    https://support.microsoft.com/en-us/...c-aa8c2a866842

    It still doesn't show available to 2007. It says:
    Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2019 Excel 2016 Excel 2019 for Mac Excel 2013 Excel 2010 Excel 2016 for Mac Excel Web App

    I wish their own support site was accurate. Oh well...

+ 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] COUNTIFS - Blank cells
    By blackburnsexcel in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-03-2021, 05:39 AM
  2. [SOLVED] CountIfs for not blank cells with condition
    By mutzie in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-17-2019, 08:17 PM
  3. CountIFS - ignore blank cells and cells with text in
    By whitebits in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-25-2018, 12:43 AM
  4. Countifs Returning Value Error with Blank Cells
    By Troy73 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-13-2017, 08:44 AM
  5. Exclude blank cells from COUNTIFS formula
    By Leahsco in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 08-05-2014, 11:45 AM
  6. [SOLVED] COUNTIFS for non-blank cells
    By DSwartz in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-19-2013, 03:39 PM
  7. Countifs should return blank results for blank row of cells
    By Groovicles in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-29-2013, 06:20 PM

Tags for this Thread

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