Countif not ="" or contains no formula

facethegod

Well-known Member

Joined

Aug 1, 2006

Messages

767

• Jun 11, 2007

• #2

Try this

Code:

=SUM((ISBLANK(A1:A9))*1)

confirm w/ Ctrl + shift + Enter
Hope this helps

 

Upvote 0

Lewiy

Well-known Member

Joined

Jan 5, 2007

Messages

4,284

• Jun 11, 2007

• #3

=COUNTBLANK(A1:A9)

 

Upvote 0

Jonmo1

MrExcel MVP

Joined

Oct 12, 2006

Messages

44,061

• Jun 11, 2007

• #4

=COUNTBLANK(A1:A9)

 

Upvote 0

doco

Active Member

Joined

May 4, 2006

Messages

262

• Jun 11, 2007

• #5

Code:

{=COUNT(IF($A$1:$A$9<>"",$A$1:$A$9))}

As an array formula this works..

 

Upvote 0

G

goldfish

Well-known Member

Joined

Aug 23, 2005

Messages

712

• Jun 11, 2007

• #6

2 points of correction:

1. I meant to say I want to count the cells that DO have visible data

2. This is for a defined name, so array formulas (I believe) will not work.

I'm just trying to do something like
=OFFSET($A$1:$A$9,0,0,SOMECOUNT($A$1:$A$9),1)
as a defined range to select the data in the range (yes the data will all be at the top, but sometime the blanks are ="" and sometimes they have no formula, and I want to account for both)

Thanks,
~GoldFish

 

Upvote 0

Jonmo1

MrExcel MVP

Joined

Oct 12, 2006

Messages

44,061

• Jun 11, 2007

• #7

=COUNTA(A1:A9)

 

Upvote 0

G

goldfish

Well-known Member

Joined

Aug 23, 2005

Messages

712

• Jun 11, 2007

• #8

=COUNTA(A1:A9)

Click to expand...

CountA works blank cells contain no data, but countA also counts cells that are ="", which I don't want.

IF I have

Code:

A1: DATA1
A2: DATA2
A3: DATA3
A4: =""
A5:

I would like the count to be 3, and no combination of COUNT, COUNTA and COUNTBLANK has worked for me.

Thanks,
Gold Fish

 

Upvote 0

Richard Schollar

MrExcel MVP

Joined

Apr 19, 2005

Messages

23,707

• Jun 11, 2007

• #9

Hi

Try:

Code:

=COUNTIF($A$1:$A$9,"?*")

 

Upvote 0

G

goldfish

Well-known Member

Joined

Aug 23, 2005

Messages

712

• Jun 11, 2007

• #10

Hi

Try:

Code:

=COUNTIF($A$1:$A$9,"?*")

Click to expand...

Thanks! That worked brilliantly.

Thanks to all those that tried to help as well.

 

Upvote 0