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://"http://www.excelguru.ca/forums/showthread.php?5522-Countif-excluding-blank-cells"


    http://www.excelguru.ca/forums/showthread.php?5522-Countif-excluding-blank-cells

  • Re: Countif excluding blank cells


    Hi,


    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:
    =SUM(--(ISBLANK(A2:A43)))


    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:
    =SUMPRODUCT(--(Sheet1!D:D=Sheet2!A2),--(Sheet1!A:A<>""))


    Hope this helps.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

Participate now!

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