How to Reference "" (Blank) in an IF formula

[apmale]

I can't seem to figure out to cause an IF formula to utilize 'blank' as a referenced criteria. Here is a simplified version of the problem:

Column A
Criterion = 1
Criterion = 2
Criterion = 3
Criterion = Blank

=countif(Range,[criterion]) - this works for rows 1 - 3
=countif(Range, "") - this works for row 4

I want to be able to use the first formula with row 4, too, but it doesn't work with any of the syntaxes I've been trying (e.g., """", isblank).

I'd appreciate any thoughts on how or if this can be done. Thanks!

 

[Andrew Poulsom]

What's [criterion]?

 

[FormR]

Try entering this as the criterion; ie in cell A4 enter:
=""

 

[Andrew Poulsom]

Oh I understand now. Try:

=COUNTIF(Range,"="&A4)

 

[iyyi]

If the range is B1:B10, and the criterion cell E1 then try:
=COUNTIF(B1:B10,IF(E1="","",E1))

 

[MarthMV]

I almost always use "" as well. Very useful in combination with if formulas.

 

[apmale]

Thanks, guys. Let me restate as my original example wasn't clear:

- I have an array called 'loandata', which has 500 rows. 300 are 'loan a', 100 are 'loan b', 75 are 'loan c' and 25 are blank. The formulas i'm using now are:

Column A
Loan A =countif(loandata,a1) = 300
Loan B =countif(loandata,a2) = 100
Loan C =countif(loandata,a3) = 75
"" = countif(loandata,"") = 25
= countif(loandata,a4) = 0

So, the formula with the "" included works, but the reference to "" does not work. I tried Andrew's suggestion of "="&A4, but that didn't seem to work. thanks again for your insights.

 

[FormR]

It appears to work for me (as does my suggestion)

Excel Workbook
	A	B	C	D
1	A	4		A
2	B	5		A
3	C	6		A
4		7		A
5				B
6				B
7
8
9
10
11				B
12				B
13				B
14
15
16				C
17				C
18				C
19				C
20				C
21
22				C
Sheet1

#VALUE!

</td></tr></table></td></tr></table>

 

[pgc01]

Hi

Try:

=COUNTBLANK(loandata)

This will count all the empty cells and those with empty strings in the range loandata.

 

[apmale]

Andrew / R4,

I understand your recommendation now; it worked perfectly; thank you very much.