COUNTIF and Ignore Blank Cells

[BristolBay]

Im trying to write this formula below to also ignore blank cells. Any help would be appreciated.

=IF(COUNTIF(F$14:F$23;I14);COUNTIF(F$14:F$23;I14);"")

 

[mikerickson]

Can you give us a small data set with the desired result and what you are getting. COUNTIF should not count blank cells unless I14 is blank.

 

[Tim_Excel_]

Welcome to the forum,

The formula you gave is incorrect. To see how to fill in the formula, highlight the cell with the formula and click on the IF function in the formula bar.

I'm a little confused as to what the problem is. A COUNTIF function will only look for the value in I14, so unless I14 is empty no empty cells will be counted by this formula.

 

[Scott Huish]

There is nothing incorrect about the formula provided. Semicolons are used in foreign versions of Excel.

The formula as written would show the value if any results were counted (Numerically 0 equates to false, non-zero to true), else blank

 

[Tim_Excel_]

Thanks Scott, that was unknown to me

 

[BristolBay]

The formula is part of an auto generated bill sent to customers. As some of you mentioned the reason why COUNTIF is counting blank cells is because the criteria cells is column "I" that contain the same formula are blank.

So COUNTIF is counting blank cells because the criteria is blank (I think), in this case the number of blank cells is 4. What I am trying to figure out is how to count those cells in column "F" when there are sometimes blanks. I am open to other suggestions and ways of accomplishing this, and yes I am in Europe so my Excel uses Semicolons. See my example below:

[/IMG]

Original Formula: =IF(COUNTIF(F$14:F$23;I14);COUNTIF(F$14:F$23;I14);"")

 

[mikerickson]

Try
=IF(LEN(I4)*COUNTIF(F$14:F$23;I14);COUNTIF(F$14:F$23;I14);"")

 

[BristolBay]

I tested it and it doesn't seem to work. I get a blank cell as a result when using the formula in column "J".

 

[mikerickson]

Ooops, I missed a 1. Try LEN(I14)

 

[BristolBay]

That Works! Thank you very much.