Forum Discussion

Edg38426's avatar
Edg38426
Brass Contributor
Dec 23, 2021
Solved

How to ignore formulas that return blank cells in COUNTIF

I have a worksheet (worksheet "A") where one column is pulling dates using a VLOOKUP formula from another worksheet (worksheet "B"). On another worksheet (worksheet "C"), I am trying to count the cells in worksheet "A" using COUNTIFS, but it keeps including in its count the cells that, though they appear blank, actually contain the VLOOKUP formula. Is there any way to include a parameter in my COUNTIFS formula that will tell it to ignore these "blank" cells that include the VLOOKUP formulas? NOTE: I did try and use a COUNTBLANKS in conjunction with SUM to remove the number of blank cells from the total count, however my COUNTIFS formula is differentiating between criteria that I cannot do using SUM and COUNTBLANKS. I attached an example workbook (with multiple worksheets) if it helps. 

  • Edg38426 

    Try

     

    =COUNTIFS(Table2[Spec/ Build],"*spec*",Table2[Permit Rcvd],">0",Table2[P.O. Release],">0",Table2[GL],"0")

     

    or

     

    =SUMPRODUCT(ISNUMBER(SEARCH("spec",Table2[Spec/ Build]))*(Table2[Permit Rcvd]<>"")*(Table2[P.O. Release]<>"")*(Table2[GL]=0))

2 Replies

  • Edg38426 

    Try

     

    =COUNTIFS(Table2[Spec/ Build],"*spec*",Table2[Permit Rcvd],">0",Table2[P.O. Release],">0",Table2[GL],"0")

     

    or

     

    =SUMPRODUCT(ISNUMBER(SEARCH("spec",Table2[Spec/ Build]))*(Table2[Permit Rcvd]<>"")*(Table2[P.O. Release]<>"")*(Table2[GL]=0))

    • Edg38426's avatar
      Edg38426
      Brass Contributor
      That first solution is perfect, since the value being returned is "0" anyways. Thanks for the help! I'm a little sad that I didn't realize that myself! This forum has been very helpful for me!

Resources