Countif that does not count blank cells

[BMil8]

Hello,

I'm trying to insert a formula that counts the number of values in a column that does not contain the word "Vendor". Currenlty my formula looks as follows:

=COUNTIF('Atlanta N, GA - Open'!B:B,"<>*Vendor*")

However this counts the blank cells as well since they do not contain "Vendor". Is there a formula that will ignore the blank cells and only reference cells that contain a value?

Thanks,
Brian

 

[jeffreybrown]

Hi Brian,

How about...

=COUNTA('Atlanta N, GA - Open'!B:B)-COUNTIF('Atlanta N, GA - Open'!B:B,"*Vendor*")

 

[marka87uk]

Why not wrap the formula in an IF statement?

Edit - Sorry I misread the question!

 

[T. Valko]

Hello,

I'm trying to insert a formula that counts the number of values in a column that does not contain the word "Vendor". Currenlty my formula looks as follows:

=COUNTIF('Atlanta N, GA - Open'!B:B,"<>*Vendor*")

However this counts the blank cells as well since they do not contain "Vendor". Is there a formula that will ignore the blank cells and only reference cells that contain a value?

Thanks,
Brian

What version of Excel are you using?

 

[abmati]

Please enter a formula in next column and copy it then count that column>0

Excel Workbook
	A	B
1	111Vendor11	4
2	111xyxxyz11	0
Sheet1

Regards.

 

[BMil8]

I'm using 2007. Jeff's formula worked great (unless you know of another way to condense the formula).

 

[T. Valko]

I'm using 2007. Jeff's formula worked great (unless you know of another way to condense the formula).

Like this...

=COUNTIFS('Atlanta N, GA - Open'!B:B,"<>*Vendor*",'Atlanta N, GA - Open'!B:B,"<>")

 

[BMil8]

Works great too. Thanks to everybody for the help. I wish I knew more about Excel.

 

[abmati]

AMENDMENT:
Please enter a formula in next column and copy it then count that column>0

Excel Workbook
	A	B
1	111Vendor11	4
2		0
3	111xyxxyz11	0
Sheet1

Regards,

 

[T. Valko]

Works great too. Thanks to everybody for the help. I wish I knew more about Excel.

You're welcome. Thanks for the feedback!