Countif excluding blank cells

  • Hello,
    In the attached example, I have two workesheets - Sheet1 where I have some customer information and sheet2 where I want to count the number of times a customer appears on Sheet1.

    On Sheet2, I want a result that shows number of times a customer appeared on Sheet1 excluding the times when column A is blank on Sheet 1. I have the following formula, but it is giving a wrong result.

    =COUNTIFS(Sheet1!D:D,Sheet2!A2,Sheet1!A:A,"<>"&" ")

    It gives a result of 42 whereas the result should be 39.

    Can someone please help to fix this?
    Many Thankshttp://""

  • Re: Countif excluding blank cells


    Excel obviously thinks there is something in the 'blank' cells, even though a test for ="" returns True, as if you select cell A1 and hit Ctrl + down arrow it takes you to the bottom of the column of data. If the 'blank' cells were completely blank it would stop at A14. I assume you have probably imported the data from another system.

    Similarly, if you create this array formula anywhere in Sheet1 it returns zero:

    If you go into the three 'blank' cells and press Delete, the result of your formula changes.

    This seems to work without clearing the cells:

    Hope this helps.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!