I wish to construct Auction Lists for my Philatelic Society. At present I
make the list up in an Acces DB and then transfer it to Excel (2003). At
this point I add extra columns to cater for "Bidders #'s"; "Selling price";
"Price to Buyer";"Return to Vendor"; & "Return to Club", complete with
relevant formulae. So far so good, I now wish to add, at the base of these
columns, via a macro initially, a summary of data as entered. I also want to
show, and use, in order to locate this data, the "TOTAL NUMBER OF LOTS".
Simple, just count the "Lot #" column, no joy at all, and there doesn't
appear to be an answer in the HELP files. Has any body any ideas.
I could I suppose enter this number from the Access Database but would like
to make it fully self-supporting.

Dave

=COUNTA(range) will return a count of all non-blank cells in the range.

=COUNT(range) will return just the numerical cells in the range.


Gord Dibben Excel MVP

On Thu, 24 Nov 2005 08:17:09 -0800, "Dave Hacker" <Dave
wrote:

I wish to construct Auction Lists for my Philatelic Society. At present I
make the list up in an Acces DB and then transfer it to Excel (2003). At
this point I add extra columns to cater for "Bidders #'s"; "Selling price";
"Price to Buyer";"Return to Vendor"; & "Return to Club", complete with
relevant formulae. So far so good, I now wish to add, at the base of these
columns, via a macro initially, a summary of data as entered. I also want to
show, and use, in order to locate this data, the "TOTAL NUMBER OF LOTS".
Simple, just count the "Lot #" column, no joy at all, and there doesn't
appear to be an answer in the HELP files. Has any body any ideas.
I could I suppose enter this number from the Access Database but would like
to make it fully self-supporting.

Gord: The point is that theoretically I do not know the range. Is there a
way I can return the first blank row number as a result, this could then be
manipulated

"Gord Dibben" wrote:

Dave

=COUNTA(range) will return a count of all non-blank cells in the range.

=COUNT(range) will return just the numerical cells in the range.


Gord Dibben Excel MVP

On Thu, 24 Nov 2005 08:17:09 -0800, "Dave Hacker" <Dave
wrote:

I wish to construct Auction Lists for my Philatelic Society. At present I
make the list up in an Acces DB and then transfer it to Excel (2003). At
this point I add extra columns to cater for "Bidders #'s"; "Selling price";
"Price to Buyer";"Return to Vendor"; & "Return to Club", complete with
relevant formulae. So far so good, I now wish to add, at the base of these
columns, via a macro initially, a summary of data as entered. I also want to
show, and use, in order to locate this data, the "TOTAL NUMBER OF LOTS".
Simple, just count the "Lot #" column, no joy at all, and there doesn't
appear to be an answer in the HELP files. Has any body any ideas.
I could I suppose enter this number from the Access Database but would like
to make it fully self-supporting.

Dave,

As you have only one group of data, you can use a whole column as the range,
since COUNTA counts non-blank cells, like so =COUNTA(A:A). It might be 1 or
2 too many for headings , if so just subtract that number in the formula.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dave Hacker" wrote in message
...
Gord: The point is that theoretically I do not know the range. Is there a
way I can return the first blank row number as a result, this could then
be
manipulated

"Gord Dibben" wrote:

Dave

=COUNTA(range) will return a count of all non-blank cells in the range.

=COUNT(range) will return just the numerical cells in the range.


Gord Dibben Excel MVP

On Thu, 24 Nov 2005 08:17:09 -0800, "Dave Hacker" <Dave
wrote:

I wish to construct Auction Lists for my Philatelic Society. At present
I
make the list up in an Acces DB and then transfer it to Excel (2003).
At
this point I add extra columns to cater for "Bidders #'s"; "Selling
price";
"Price to Buyer";"Return to Vendor"; & "Return to Club", complete with
relevant formulae. So far so good, I now wish to add, at the base of
these
columns, via a macro initially, a summary of data as entered. I also
want to
show, and use, in order to locate this data, the "TOTAL NUMBER OF
LOTS".
Simple, just count the "Lot #" column, no joy at all, and there doesn't
appear to be an answer in the HELP files. Has any body any ideas.
I could I suppose enter this number from the Access Database but would
like
to make it fully self-supporting.