Forum Discussion
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.
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
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))
- Edg38426Brass ContributorThat 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!