Hi all
In Column AA rows 8 thru 13 I have a list of names. These names vary as we
move along Columns
In AA14 I would like to place a countif formula to give the total number of
names in each column

Along the lines of =countif(aa8:aa13,istext)

Any help welcome

Best

Stew

What do the cells without names have in them? Are they blank or "" or
something else?

You could use:
(i) COUNTA to count all non-blank cells; or
(ii) 6-COUNTIF(AA8:AA13,istext) where istext is ""

Hi Francis and Test

I can see now that this is going to be more difficult than I thought.
In aa8 - aa13 We have a Formula
=IF(AA$3="NONE","",IF(RIGHT(INDIRECT("'Personel
'!"&ADDRESS(4,(INT(ROW()/1)-6))),6)="MEMBER"," ",INDIRECT("'Personel
'!"&ADDRESS(4,(INT(ROW()/1)-6)))))

The result of which, as you can see, displays a blank or a name.

All Counta or Countif Formua appear to be counting the Formula as a text
string. This is my problem
I do not want the Formula counted only the result of the formula

Thanks for your time

Best

Stew
"Test" wrote:

What do the cells without names have in them? Are they blank or "" or
something else?

You could use:
(i) COUNTA to count all non-blank cells; or
(ii) 6-COUNTIF(AA8:AA13,istext) where istext is ""

Use the formula "=6-COUNTIF(AA8:AA13," ")"

Hi Test

I Used It as shown But it gives the result 6 if there is 6 names or 6 if
there is no names!!

Any other thoughts

Stew

"Test" wrote:

Use the formula "=6-COUNTIF(AA8:AA13," ")"

Change it to "=6-COUNTIF(AA8:AA13,"")" and change your formulas so that " "
equals "" so the blank results are consistent.

On 3/14/09 10:52 AM, stew wrote:

Hi Francis and Test

I can see now that this is going to be more difficult than I thought.
In aa8 - aa13 We have a Formula
=IF(AA$3="NONE","",IF(RIGHT(INDIRECT("'Personel
'!"&ADDRESS(4,(INT(ROW()/1)-6))),6)="MEMBER"," ",INDIRECT("'Personel
'!"&ADDRESS(4,(INT(ROW()/1)-6)))))

The result of which, as you can see, displays a blank or a name.

All Counta or Countif Formua appear to be counting the Formula as a text
string. This is my problem
I do not want the Formula counted only the result of the formula

Thanks for your time

Best

Stew
"Test" wrote:

What do the cells without names have in them? Are they blank or "" or
something else?

You could use:
(i) COUNTA to count all non-blank cells; or
(ii) 6-COUNTIF(AA8:AA13,istext) where istext is ""


Stew,
This should do what you want:

=ROWS(AA8:AA13)-COUNTBLANK(AA8:AA13)

COUNTA, ISBLANK, etc. examines the contents of cells. Formulas count as
part of the contents and therefore are not empty.

COUNTBLANK counts cells that are actually empty cells plus cells with
formulas that return "", as in your situation.
--
Art

Thank you Art Test and Francis for your assistance and Efforts
That has doe the trick, And I understand as well!!

Best

Stew

"Art" wrote:

On 3/14/09 10:52 AM, stew wrote:

Hi Francis and Test

I can see now that this is going to be more difficult than I thought.
In aa8 - aa13 We have a Formula
=IF(AA$3="NONE","",IF(RIGHT(INDIRECT("'Personel
'!"&ADDRESS(4,(INT(ROW()/1)-6))),6)="MEMBER"," ",INDIRECT("'Personel
'!"&ADDRESS(4,(INT(ROW()/1)-6)))))

The result of which, as you can see, displays a blank or a name.

All Counta or Countif Formua appear to be counting the Formula as a text
string. This is my problem
I do not want the Formula counted only the result of the formula

Thanks for your time

Best

Stew
"Test" wrote:

What do the cells without names have in them? Are they blank or "" or
something else?

You could use:
(i) COUNTA to count all non-blank cells; or
(ii) 6-COUNTIF(AA8:AA13,istext) where istext is ""


Stew,
This should do what you want:

=ROWS(AA8:AA13)-COUNTBLANK(AA8:AA13)

COUNTA, ISBLANK, etc. examines the contents of cells. Formulas count as
part of the contents and therefore are not empty.

COUNTBLANK counts cells that are actually empty cells plus cells with
formulas that return "", as in your situation.
--
Art

try

=SUMPRODUCT(--(AA3:AA13<""))

if your existing formula return a blank, the blank will not be count
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis










"stew" wrote:

Hi Francis and Test

I can see now that this is going to be more difficult than I thought.
In aa8 - aa13 We have a Formula
=IF(AA$3="NONE","",IF(RIGHT(INDIRECT("'Personel
'!"&ADDRESS(4,(INT(ROW()/1)-6))),6)="MEMBER"," ",INDIRECT("'Personel
'!"&ADDRESS(4,(INT(ROW()/1)-6)))))

The result of which, as you can see, displays a blank or a name.

All Counta or Countif Formua appear to be counting the Formula as a text
string. This is my problem
I do not want the Formula counted only the result of the formula

Thanks for your time

Best

Stew
"Test" wrote:

What do the cells without names have in them? Are they blank or "" or
something else?

You could use:
(i) COUNTA to count all non-blank cells; or
(ii) 6-COUNTIF(AA8:AA13,istext) where istext is ""

Hi Francis

That returns a 10 where the correct answer is 6.

Art has given me =ROWS(AA8:AA13)-COUNTBLANK(AA8:AA13)
Which is doing the Business.

Thank you very much for your time and efforts

Best

Stew

"francis" wrote:

try

=SUMPRODUCT(--(AA3:AA13<""))

if your existing formula return a blank, the blank will not be count
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis










"stew" wrote:

Hi Francis and Test

I can see now that this is going to be more difficult than I thought.
In aa8 - aa13 We have a Formula
=IF(AA$3="NONE","",IF(RIGHT(INDIRECT("'Personel
'!"&ADDRESS(4,(INT(ROW()/1)-6))),6)="MEMBER"," ",INDIRECT("'Personel
'!"&ADDRESS(4,(INT(ROW()/1)-6)))))

The result of which, as you can see, displays a blank or a name.

All Counta or Countif Formua appear to be counting the Formula as a text
string. This is my problem
I do not want the Formula counted only the result of the formula

Thanks for your time

Best

Stew
"Test" wrote:

What do the cells without names have in them? Are they blank or "" or
something else?

You could use:
(i) COUNTA to count all non-blank cells; or
(ii) 6-COUNTIF(AA8:AA13,istext) where istext is ""

do you just want to have the number of count that occupy AA8 to AA13 or
you want to count a specific name in the range?

if the formal, you can use COUNTA to find the total number of names in the
range
=COUNTA(AA8:AA13)
this will counts the number of cells that are not empty in the list

if the later, try =COUNTIF(AA8:AA13,B14)
B14 is where you will input the name you want to count in the list.


--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis


"stew" wrote:

Hi all
In Column AA rows 8 thru 13 I have a list of names. These names vary as we
move along Columns
In AA14 I would like to place a countif formula to give the total number of
names in each column

Along the lines of =countif(aa8:aa13,istext)

Any help welcome

Best

Stew

Francis, I read your post using Countif() to another user. I
UNDERSTOOD! I am amazed. LOL

I have a followon question. I want to use the equivalent of COUNTIFS()
(Excel 2007). My girlfriend has 2003 and my spreadsheet is NOT backward
compatible. SO, I have write the function using a compound COUNTIF()
function or some equivalent, to evaluate two different coulumns.

For example the logic would be
IF the item in Column A = "Yes"
AND if the item in Column B = "Red"
THEN count it
OTHERWISE don't count
NEXT ROW loop to IF above.

Can you help me put this logic into a formula that will work in 2007 and 2003?

I would appreciate the help.
You can also respond back to me directly at or 314.331.7520.

Thanks much for your help.
ROn Montgomery

Try this:

=SUMPRODUCT(--(A1:A100="Yes"),--(B1:B100="Red"))

Or, using cells to hold the criteria:

D1 = Yes
E1 = Red

=SUMPRODUCT(--(A1:A100=D1),--(B1:B100=E1))

Adjust thr ranges to suit.

Note that with SUMPRODUCT you *can't* use entire columns as range references
unless you're using Excel 2007.

--
Biff
Microsoft Excel MVP


"Ron Monty" <Ron wrote in message
...

Francis, I read your post using Countif() to another user. I
UNDERSTOOD! I am amazed. LOL

I have a followon question. I want to use the equivalent of COUNTIFS()
(Excel 2007). My girlfriend has 2003 and my spreadsheet is NOT backward
compatible. SO, I have write the function using a compound COUNTIF()
function or some equivalent, to evaluate two different coulumns.

For example the logic would be
IF the item in Column A = "Yes"
AND if the item in Column B = "Red"
THEN count it
OTHERWISE don't count
NEXT ROW loop to IF above.

Can you help me put this logic into a formula that will work in 2007 and
2003?

I would appreciate the help.
You can also respond back to me directly at or
314.331.7520.

Thanks much for your help.
ROn Montgomery

=counta(AA8:AA13)

will count the number of cells that have something in them. A formula that
evaluates to "" will be counted.

A cell that contained a formula that evaluated to "" and converted to values
will be counted.

An empty cell (edit|Clear|contents or hitting the delete key to clear the cell)
will not be counted

stew wrote:

Hi all
In Column AA rows 8 thru 13 I have a list of names. These names vary as we
move along Columns
In AA14 I would like to place a countif formula to give the total number of
names in each column

Along the lines of =countif(aa8:aa13,istext)

Any help welcome

Best

Stew

--

Dave Peterson