Countif Advise

In A1 on Sheet2 enter:

=COUNTIF(Sheet1!A5:INDEX(Sheet1!A:A,MATCH(REPT("z" ,255),Sheet1!A:A)),"=Dave"
)

"Michael168" wrote in message
...
May I ask for advice on how to auto update sheet2?
That is I have my data in sheet1. My answer in sheet2.
In sheet2 cell A1 I have the count value of sheet1 range A5:A1815.
So when I add new row of data in sheet1 the new range will be
A5:A1816.
How can I make Cell A1 in sheet2 auto update the count to A5:A1816
instead of manually changing the range everytime I make an update of
rows in sheet A1.

e.g.

On Sheet1 the column A range consists of different names.
On sheet2 the cell A1 was use to count / total no times the same names
appear in column A

=COUNTIF(Sheet1!A5:A1815,"=Dave")

Thank you for your advice and sorry for the troubles caused.

With Regards
Michael168



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

One way

=COUNTIF(OFFSET(Sheet1!$A$5,,,COUNTA(Sheet1!$A$5:$ A$65536),1),"Dave")

if you do insertnamedefine and put a name in the name box and

=OFFSET(Sheet1!$A$5,,,COUNTA(Sheet1!$A$5:$A$65536) ,1)

in the refers to box you can use

=COUNTIF(MyRange,"Dave")

instead

--

Regards,

Peo Sjoblom

"Michael168" wrote in message
...
May I ask for advice on how to auto update sheet2?
That is I have my data in sheet1. My answer in sheet2.
In sheet2 cell A1 I have the count value of sheet1 range A5:A1815.
So when I add new row of data in sheet1 the new range will be
A5:A1816.
How can I make Cell A1 in sheet2 auto update the count to A5:A1816
instead of manually changing the range everytime I make an update of
rows in sheet A1.

e.g.

On Sheet1 the column A range consists of different names.
On sheet2 the cell A1 was use to count / total no times the same names
appear in column A

=COUNTIF(Sheet1!A5:A1815,"=Dave")

Thank you for your advice and sorry for the troubles caused.

With Regards
Michael168



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Thanks for your advise.

Regards
Michael168



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/