Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF-look for dates in range
I have a spreadsheet where there are several cells, each containing a date.
I would like to set up a formula that counts how many of thsoe cells contain dates within each month. So, I want to count the number of cells that contain dates from 1/1-1/31, 2/1-2/28, etc. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF-look for dates in range
You could do it like this:
=COUNTIF(D:D,"="&E1)-COUNTIF(D:D""&F1) where I've assumed column D contains your dates, and that you put your start date in cell E1 and the finish date in cell F1. If you put the formula in G1, then you can copy it down with other dates in E2:F2 etc. Hope this helps. Pete On Feb 21, 12:31*am, richzip wrote: I have a spreadsheet where there are several cells, each containing a date. * I would like to set up a formula that counts how many of thsoe cells contain dates within each month. *So, I want to count the number of cells that contain dates from 1/1-1/31, 2/1-2/28, etc. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF-look for dates in range
My preference is to use two countif functions for each range. For example,
to count the dates in January 2009, I'd count all those on or after Jan 1 09 and subtract the count of those on or after Feb 1 09: =countif(range,"=" & date(2009,1,1)) - countif(range,"=" & date(2009,2,1)) "richzip" wrote: I have a spreadsheet where there are several cells, each containing a date. I would like to set up a formula that counts how many of thsoe cells contain dates within each month. So, I want to count the number of cells that contain dates from 1/1-1/31, 2/1-2/28, etc. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF-look for dates in range
Hi,
Try this: =SUMPRODUCT(--(MONTH(A$1:A$200)=D1)) where D1 contains 1 for January and your dates are in A1:A200. Note that this is not year specific - that means it will count the number of January dates in any year. You can enter the numbers 1-12 in D1:D12 and copy the formula down. -- If this helps, please click the Yes button Cheers, Shane Devenshire "richzip" wrote: I have a spreadsheet where there are several cells, each containing a date. I would like to set up a formula that counts how many of thsoe cells contain dates within each month. So, I want to count the number of cells that contain dates from 1/1-1/31, 2/1-2/28, etc. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF-look for dates in range
Hi,
If you need to handle years then =SUMPRODUCT(--(TEXT(A$1:A$200,"MY")=G1)) In this case you enter your date in as text in G1 as 109 meaning Jan 09. You can enter the date as text you typing '109 (putting an apostrophy in front of the number. There are many variations of this idea depending on how you want to indicate the date. -- If this helps, please click the Yes button Cheers, Shane Devenshire "richzip" wrote: I have a spreadsheet where there are several cells, each containing a date. I would like to set up a formula that counts how many of thsoe cells contain dates within each month. So, I want to count the number of cells that contain dates from 1/1-1/31, 2/1-2/28, etc. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif Function for dates in a range | Excel Worksheet Functions | |||
Identifying unique dates in a range of cells containing dates... | Excel Discussion (Misc queries) | |||
Identifying unique dates within a range of cells containing dates | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions |