Microsoft Office Forums Count the Number of Cells Containling Dates
 Register FAQ Search Today's Posts Mark Forums Read

#1
05-19-2019, 07:05 AM
 skeezix Windows 10 Office 97-2003 Advanced Beginner Join Date: Jan 2019 Posts: 44
Count the Number of Cells Containling Dates

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?
#2
05-19-2019, 09:20 AM
 Logit Windows 10 Office 2007 Competent Performer Join Date: Jan 2017 Posts: 245

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")
#3
05-19-2019, 10:46 AM
 xor Windows 10 Office 2016 Expert Join Date: Oct 2015 Posts: 1,027

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.
#4
05-19-2019, 02:14 PM
 skeezix Windows 10 Office 97-2003 Advanced Beginner Join Date: Jan 2019 Posts: 44

Quote:
 Originally Posted by Logit 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")

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.
#5
05-19-2019, 02:23 PM
 skeezix Windows 10 Office 97-2003 Advanced Beginner Join Date: Jan 2019 Posts: 44

Quote:
 Originally Posted by xor In cell B1: =(A1>=\$C\$1)*(A1<=\$D\$1)*(CELL("format";A1)="D1") copied down to B10 and then =SUM(B1:B10)
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, 32 views)
#6
05-19-2019, 10:02 PM
 xor Windows 10 Office 2016 Expert Join Date: Oct 2015 Posts: 1,027

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.
#7
05-20-2019, 03:17 AM
 ArviLaanemets Windows 8 Office 2016 Expert Join Date: May 2017 Posts: 448

Quote:
 Originally Posted by Logit =COUNTIF(A:A,">1/1/1900")
OP can as well use
Code:
`COUNTIF(A:A,">0")`
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.
#8
05-20-2019, 07:49 AM
 skeezix Windows 10 Office 97-2003 Advanced Beginner Join Date: Jan 2019 Posts: 44

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!
#9
05-21-2019, 06:57 AM
 skeezix Windows 10 Office 97-2003 Advanced Beginner Join Date: Jan 2019 Posts: 44

>>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 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!
#10
05-22-2019, 05:18 AM
 Marcia Windows 7 32bit Office 2013 Competent Performer Join Date: May 2018 Location: Philippines Posts: 192

[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.
#11
05-22-2019, 11:28 AM
 skeezix Windows 10 Office 97-2003 Advanced Beginner Join Date: Jan 2019 Posts: 44

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)...
#12
05-22-2019, 11:46 AM
 Logit Windows 10 Office 2007 Competent Performer Join Date: Jan 2017 Posts: 245

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

Code:
`=COUNTIF(M:M,">1/1/1900"`
#13
05-22-2019, 11:52 PM
 ArviLaanemets Windows 8 Office 2016 Expert Join Date: May 2017 Posts: 448

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.

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, 3 views)
#14
05-25-2019, 06:11 AM
 skeezix Windows 10 Office 97-2003 Advanced Beginner Join Date: Jan 2019 Posts: 44

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

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post Kubi Excel 4 08-24-2017 05:53 PM Kubi Excel 2 08-06-2017 08:54 PM slaycock Word VBA 0 02-18-2017 07:00 AM Shahzad Excel 3 06-26-2010 01:06 AM danbenedek Excel 0 06-15-2010 12:40 AM

All times are GMT -7. The time now is 10:30 AM.

 -- Default Style -- Lightweight Contact Us - Privacy Statement - Top