Microsoft Office Forums Count the Number of Cells Containling Dates

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-19-2019, 07:05 AM
skeezix skeezix is offline Count the Number of Cells Containling Dates Windows 10 Count the Number of Cells Containling Dates Office 97-2003
Advanced Beginner
Count the Number of Cells Containling Dates
 
Join Date: Jan 2019
Posts: 44
skeezix is on a distinguished road
Default 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?
Reply With Quote
  #2  
Old 05-19-2019, 09:20 AM
Logit Logit is offline Count the Number of Cells Containling Dates Windows 10 Count the Number of Cells Containling Dates Office 2007
Competent Performer
 
Join Date: Jan 2017
Posts: 245
Logit is on a distinguished road
Default

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")
Reply With Quote
  #3  
Old 05-19-2019, 10:46 AM
xor xor is offline Count the Number of Cells Containling Dates Windows 10 Count the Number of Cells Containling Dates Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,027
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

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.
Reply With Quote
  #4  
Old 05-19-2019, 02:14 PM
skeezix skeezix is offline Count the Number of Cells Containling Dates Windows 10 Count the Number of Cells Containling Dates Office 97-2003
Advanced Beginner
Count the Number of Cells Containling Dates
 
Join Date: Jan 2019
Posts: 44
skeezix is on a distinguished road
Default

Quote:
Originally Posted by Logit View Post
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.
Reply With Quote
  #5  
Old 05-19-2019, 02:23 PM
skeezix skeezix is offline Count the Number of Cells Containling Dates Windows 10 Count the Number of Cells Containling Dates Office 97-2003
Advanced Beginner
Count the Number of Cells Containling Dates
 
Join Date: Jan 2019
Posts: 44
skeezix is on a distinguished road
Default

Quote:
Originally Posted by xor View Post
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
File Type: jpg Excel example.jpg (139.7 KB, 32 views)
Reply With Quote
  #6  
Old 05-19-2019, 10:02 PM
xor xor is offline Count the Number of Cells Containling Dates Windows 10 Count the Number of Cells Containling Dates Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,027
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

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.
Reply With Quote
  #7  
Old 05-20-2019, 03:17 AM
ArviLaanemets ArviLaanemets is offline Count the Number of Cells Containling Dates Windows 8 Count the Number of Cells Containling Dates Office 2016
Expert
 
Join Date: May 2017
Posts: 448
ArviLaanemets will become famous soon enoughArviLaanemets will become famous soon enough
Default

Quote:
Originally Posted by Logit View Post
=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.
Reply With Quote
  #8  
Old 05-20-2019, 07:49 AM
skeezix skeezix is offline Count the Number of Cells Containling Dates Windows 10 Count the Number of Cells Containling Dates Office 97-2003
Advanced Beginner
Count the Number of Cells Containling Dates
 
Join Date: Jan 2019
Posts: 44
skeezix is on a distinguished road
Default

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!
Reply With Quote
  #9  
Old 05-21-2019, 06:57 AM
skeezix skeezix is offline Count the Number of Cells Containling Dates Windows 10 Count the Number of Cells Containling Dates Office 97-2003
Advanced Beginner
Count the Number of Cells Containling Dates
 
Join Date: Jan 2019
Posts: 44
skeezix is on a distinguished road
Default

>>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!
Reply With Quote
  #10  
Old 05-22-2019, 05:18 AM
Marcia Marcia is offline Count the Number of Cells Containling Dates Windows 7 32bit Count the Number of Cells Containling Dates Office 2013
Competent Performer
 
Join Date: May 2018
Location: Philippines
Posts: 192
Marcia is on a distinguished road
Default

[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.
Reply With Quote
  #11  
Old 05-22-2019, 11:28 AM
skeezix skeezix is offline Count the Number of Cells Containling Dates Windows 10 Count the Number of Cells Containling Dates Office 97-2003
Advanced Beginner
Count the Number of Cells Containling Dates
 
Join Date: Jan 2019
Posts: 44
skeezix is on a distinguished road
Default

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)...
Reply With Quote
  #12  
Old 05-22-2019, 11:46 AM
Logit Logit is offline Count the Number of Cells Containling Dates Windows 10 Count the Number of Cells Containling Dates Office 2007
Competent Performer
 
Join Date: Jan 2017
Posts: 245
Logit is on a distinguished road
Default

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"
Reply With Quote
  #13  
Old 05-22-2019, 11:52 PM
ArviLaanemets ArviLaanemets is offline Count the Number of Cells Containling Dates Windows 8 Count the Number of Cells Containling Dates Office 2016
Expert
 
Join Date: May 2017
Posts: 448
ArviLaanemets will become famous soon enoughArviLaanemets will become famous soon enough
Default

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
File Type: xlsx EntryCount.xlsx (14.1 KB, 3 views)
Reply With Quote
  #14  
Old 05-25-2019, 06:11 AM
skeezix skeezix is offline Count the Number of Cells Containling Dates Windows 10 Count the Number of Cells Containling Dates Office 97-2003
Advanced Beginner
Count the Number of Cells Containling Dates
 
Join Date: Jan 2019
Posts: 44
skeezix is on a distinguished road
Default

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.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count the Number of Cells Containling Dates count the number of text in a cell based off a different cells text Kubi Excel 4 08-24-2017 05:53 PM
Count the Number of Cells Containling Dates count a number of cells based on the beginning of a order number Kubi Excel 2 08-06-2017 08:54 PM
Distribute text in one cell across a range of cells (overcoming selection.range.cells.count bug) slaycock Word VBA 0 02-18-2017 07:00 AM
How to Count from Different Cells? Shahzad Excel 3 06-26-2010 01:06 AM
Count range cells eliminating merge cells danbenedek Excel 0 06-15-2010 12:40 AM


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


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft