| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting the number of date entries in a giving month
I have a very simple spreadsheet the records the date a document is created
and records dates in the documents history what i was to do is counts the number of documents created by month so i can put this information into a bar chart eg if i have 200 dates in a column i want to know how many dates are in Jan, Feb, March etc... Hope this makes sense Thanks in advance Phil G |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting the number of date entries in a giving month
=SUMPRODUCT(--(MONTH(A1:A100)=1))
Dates in column A and month of January If you have more than one year you can check year: =SUMPRODUCT(--(MONTH(A1:A100)=1),--(YEAR(A1:A100)=2006)) HTH "Philgr" wrote: I have a very simple spreadsheet the records the date a document is created and records dates in the documents history what i was to do is counts the number of documents created by month so i can put this information into a bar chart eg if i have 200 dates in a column i want to know how many dates are in Jan, Feb, March etc... Hope this makes sense Thanks in advance Phil G |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting the number of date entries in a giving month
Excelent that works :)
How do i get it to ignore the empty cells , eg if i have 82 entries in the coloum A1:A200 Currently it reports the empty cells as Jan ? "Toppers" wrote: =SUMPRODUCT(--(MONTH(A1:A100)=1)) Dates in column A and month of January If you have more than one year you can check year: =SUMPRODUCT(--(MONTH(A1:A100)=1),--(YEAR(A1:A100)=2006)) HTH "Philgr" wrote: I have a very simple spreadsheet the records the date a document is created and records dates in the documents history what i was to do is counts the number of documents created by month so i can put this information into a bar chart eg if i have 200 dates in a column i want to know how many dates are in Jan, Feb, March etc... Hope this makes sense Thanks in advance Phil G |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting the number of date entries in a giving month
Try the following modification:
=SUMPRODUCT(--(MONTH(A1:A100)=1)) - COUNTBLANK(A1:A100) That should take care of blanks. HTH, TK "Philgr" wrote: Excelent that works :) How do i get it to ignore the empty cells , eg if i have 82 entries in the coloum A1:A200 Currently it reports the empty cells as Jan ? "Toppers" wrote: =SUMPRODUCT(--(MONTH(A1:A100)=1)) Dates in column A and month of January If you have more than one year you can check year: =SUMPRODUCT(--(MONTH(A1:A100)=1),--(YEAR(A1:A100)=2006)) HTH "Philgr" wrote: I have a very simple spreadsheet the records the date a document is created and records dates in the documents history what i was to do is counts the number of documents created by month so i can put this information into a bar chart eg if i have 200 dates in a column i want to know how many dates are in Jan, Feb, March etc... Hope this makes sense Thanks in advance Phil G |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Counting the number of date entries in a giving month
=SUMPRODUCT(--(MONTH(A1:A100)=1),--(A1:A100<""))
"Philgr" wrote: Excelent that works :) How do i get it to ignore the empty cells , eg if i have 82 entries in the coloum A1:A200 Currently it reports the empty cells as Jan ? "Toppers" wrote: =SUMPRODUCT(--(MONTH(A1:A100)=1)) Dates in column A and month of January If you have more than one year you can check year: =SUMPRODUCT(--(MONTH(A1:A100)=1),--(YEAR(A1:A100)=2006)) HTH "Philgr" wrote: I have a very simple spreadsheet the records the date a document is created and records dates in the documents history what i was to do is counts the number of documents created by month so i can put this information into a bar chart eg if i have 200 dates in a column i want to know how many dates are in Jan, Feb, March etc... Hope this makes sense Thanks in advance Phil G |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Mileage Claim Formula | New Users to Excel | |||
| Date arithmetic: adding 1 month to prior end of month date | Excel Worksheet Functions | |||
| Calculate a 30-day moving average based on the last x number of entries and date | Excel Worksheet Functions | |||
| Excel: I enter date and format for date, but shows as number | Excel Discussion (Misc queries) | |||
| Counting distinct entries based on meeting month & year criteria | Excel Worksheet Functions | |||
Linear Mode
