I have a one-column list that contains 10 cells of dates. Some of the cells are empty. I would like a formula that gives me the total number of cells that have dates in them.
For example, if cells A1 through A5 have dates, and cells A6 through A8 are empty, and cells A9 through A10 have dates, the result of the forumla would be 7.

How can I do this?

CAUTION: This formula will count anything that is a number. If you column has more than dates and those entries are a number, this formula will count them as well.

=COUNTIF(A:A,">1/1/1900")

In cell B1:

=(A1>=$C$1)*(A1<=$D$1)*(CELL("format";A1)="D1")

copied down to B10 and then =SUM(B1:B10)

where C1 is the earliest allowed date and D1 the latest allowed date.
Please note that the last "D1" is not a cell reference but a format code.

Thank you for your help. That formula works except I forgot to say that row A has a word in it, and I have 17 rows. Could your formula somehow be changed to eliminate row A in the count? Please see the image in the next post.

Thank you for your help. Unfortunately I don't understand much about formulas in Excel, and so I don't think that I can use yours. Please see the image below. The formula would go in one of the blank cells at the bottom of each column.

Attached Images

Excel example.jpg (139.7 KB, 39 views)

In #1 you wrote that you had a one column list. I based my proposal on that. It requires that you use helper cells in B1:B10 as indicated.

If Logit's solution works for you then do use it, but as he mentions it counts anything that is a number.

Also if not a date.

OP can as well use
To get the formula to work for other columns it's enough to drag the formula right (right.clickinf on right lower corner of cell so that a cross is shown there, and then dragging ro right holding mouse tab down. And after that clearing the formula from abundant cells.

Didn't understand a thing about column A having a word in it? Do you mean column header? Or do dates in column A have some text before or after date? Or is in some cells a text instead of date?

And generally it is bad practice to have any summaries at bottom of data! You may have a situation where you must edit formulas whenever you add new row of data into table. Much better is have for any totals rows reserved at top of table. Additional bonus will be to use Freeze Panes feature to be Totals always displayed.

Sorry, my feeble mind is too small to comprehend all of this. I'll just continue to manually add up the cells of interest.
Thank you all for your help!

>>Much better is have for any totals rows reserved at top of table. Additional bonus will be to use Freeze Panes feature to be Totals always displayed.<<

As to your first paragraph, I know about that.
As to your second paragraph, the text is the "M" and the "U" in the shaded row at the top.
And as to your third paragraph, now why didn't I think of that??????? Little things like that are never obvious to me, and I thank you for the tip!

[QUOTE=I'll just continue to manually add up the cells of interest.
[QUOTE]
Why would you resort to manual counting when there were those who happily gave you solutions to possibly choose from? Feel free to shoot claarificatiry questions until you have understood fully the proposed solutions.

When I enter either COUNTIF(A:A,">0") or COUNTIF(A:A,">1/1/1900") into the cell, change the "A"s to "M"s, and then press Enter, I don't get a number in the cell, I get "COUNTIF(M:M,">0")" or COUNTIF(M:M,">1/1/1900").

And I haven't tried the longer formula

>>In cell B1:

=(A1>=$C$1)*(A1<=$D$1)*(CELL("format";A1)="D1")
copied down to B10 and then =SUM(B1:B10) where C1 is the earliest allowed date and D1 the latest allowed date...<<

because I would have to change D1 in 2 places of the formula every time I entered a new date in the cell (unless I'm misunderstanding something)...

There must be an equal sign in front of the formula and no spaces before the equal sign :

In attachment is an example file I composed based on picture you posted - at least as much as I understood what you want to do.

As you use Excel2003, I used Named Ranges/Dynamic Named Ranges to make the tables dynamic - i.e. you enter new data, and all formulas will adjust automatically.

Had you newer version of Excel, I'd used defined Tables instead.

The 1st sheet has the structure like yours, i.e. for every new year you add new columns into table. The problem with this setup is, that you have to edit the table every year - add new formulas. And this table will be a nightmare for you, when you need some report over several years, or over whole table grouped by some other parameter.

Next 2 sheets are for advised structure of same data. I didn't know what kind of data you have before column P, so I guessed, that there is some info which is repeated for every year. So I created e a separate sheet where you register such data once, and can read into data entry table using formulas whenever you need this.

The main idea for advised structure is, that you have a single column for certain type of data. You then can design report sheets, where user selects parameter(s) for report, and the report is created by formulas using entered parameter(s). Or in simpler cases, like yours, you can use autofilter feature and SUBTOTAL() function to get the wanted result (but with much more flexibility compared with design on 1st sheet).

Attached Files

EntryCount.xlsx (14.1 KB, 7 views)

Thank you for your reply. I'm using Excel 2000, BTW.

I'm not spending any more time with this problem, and I'll just manually count the cells that contain a date.