Excel Counting a blank cell as a value

**Barking_Mad** · 02-19-2009, 11:46 AM

Im sure this is very obvious but it's confusing me being a Excel novice....I have two columns (Q & R) in which 'W', 'L' or a blank space appears according to the following two formulas :

=IF(G9>L9,"W"," ")

=IF(L9>G9,"L"," ")

Additionally I have a formula elsewhere in the sheet that counts whether there is anything in the columns where 'W' and 'L' appear :

=COUNTA(Q9,AH9,AJ9)

=COUNTA(R9,AI9,AK9)

My problem is that the COUNTA function counts the blank space as a value. How can I make it stop!?

Thanks

**DonkeyOte** · 02-19-2009, 11:53 AM

" " is not a blank/null rather it is a space... if you want a Null use ""

However COUNTA will still count Nulls as valid.

Given your range is non contiguous things are a little more convoluted though you could just add the three together:

=(Q9<>"")+(AH9<>"")+(AJ9<>"")

(with contiguous ranges you can use COUNTIF(range,"*?") ... or even COUNTA less COUNTBLANK given the latter treats Nulls as blanks)

Excel Counting a blank cell as a value

LinkBack

Thread Tools

Rate This Thread

Display

Excel Counting a blank cell as a value

Re: Excel Counting a blank cell as a value

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions