Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF headscratcher
Hello,
I have a column of dates like below in cells A1:A11. 10/04/2007 21/08/2006 10/04/2006 16/04/2007 28/02/2005 16/01/2006 20/05/2003 01/03/2005 28/02/2005 13/10/2003 26/06/2006 What I would like to do is put a formula in the cell below (say A12) to look at the whole range (A1:A11) and then count the cells that have a date between 01/04/2007 and 31/07/2007 (in the above example it would return 2). I'm not sure if I should be using SUMPRODUCT for this as I am not too au fait with that and it's getting late in the day!! Any help greatly appreciated. Cheers, Ant |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF headscratcher
try this idea where b2 and b3 have your desired parameters.
=sumproduct((a2:a22=b2)*(a2:a22<b3)) -- Don Guillett Microsoft MVP Excel SalesAid Software wrote in message ps.com... Hello, I have a column of dates like below in cells A1:A11. 10/04/2007 21/08/2006 10/04/2006 16/04/2007 28/02/2005 16/01/2006 20/05/2003 01/03/2005 28/02/2005 13/10/2003 26/06/2006 What I would like to do is put a formula in the cell below (say A12) to look at the whole range (A1:A11) and then count the cells that have a date between 01/04/2007 and 31/07/2007 (in the above example it would return 2). I'm not sure if I should be using SUMPRODUCT for this as I am not too au fait with that and it's getting late in the day!! Any help greatly appreciated. Cheers, Ant |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF headscratcher
Try this:
=SUMPRODUCT((A1:A11=B12)*(A1:A11<=C12)) where you can put the start date in B12 and the finish date in C12, rather than putting them explicitly in the formula. Format the cell with the SP formula in as General or Number. Hope this helps. Pete On Sep 17, 4:34 pm, wrote: Hello, I have a column of dates like below in cells A1:A11. 10/04/2007 21/08/2006 10/04/2006 16/04/2007 28/02/2005 16/01/2006 20/05/2003 01/03/2005 28/02/2005 13/10/2003 26/06/2006 What I would like to do is put a formula in the cell below (say A12) to look at the whole range (A1:A11) and then count the cells that have a date between 01/04/2007 and 31/07/2007 (in the above example it would return 2). I'm not sure if I should be using SUMPRODUCT for this as I am not too au fait with that and it's getting late in the day!! Any help greatly appreciated. Cheers, Ant |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF headscratcher
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF headscratcher
A number of options:
=SUMPRODUCT(--(A1:A11=DATE(2007,4,1))*--(A1:A11<DATE(2007,7,31))) =COUNTIF(A1:A11,"="&DATE(2007,4,1))-COUNTIF(A1:A11,""&DATE(2007,7,31)) [and adjust the and = if applicable] =COUNTIF(A1:A11,"="&--"1/4/2007")-COUNTIF(A1:A11,""&--"31/7/2007") if you're confident in the unabiguity of the dates. -- David Biddulph wrote in message ps.com... Hello, I have a column of dates like below in cells A1:A11. 10/04/2007 21/08/2006 10/04/2006 16/04/2007 28/02/2005 16/01/2006 20/05/2003 01/03/2005 28/02/2005 13/10/2003 26/06/2006 What I would like to do is put a formula in the cell below (say A12) to look at the whole range (A1:A11) and then count the cells that have a date between 01/04/2007 and 31/07/2007 (in the above example it would return 2). I'm not sure if I should be using SUMPRODUCT for this as I am not too au fait with that and it's getting late in the day!! Any help greatly appreciated. Cheers, Ant |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF headscratcher
Thanks to everyone who replied! That was extremely quick!
I have gone with the simple sumproduct =SUMPRODUCT((A1:A11=B12)*(A1:A11<=C12)) (for no other reason than it was posted quickly!) and that seems to be working just fine. Many Thanks, Ant |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF headscratcher
Good choice !! <vbg
Pete On Sep 17, 5:01 pm, wrote: Thanks to everyone who replied! That was extremely quick! I have gone with the simple sumproduct =SUMPRODUCT((A1:A11=B12)*(A1:A11<=C12)) (for no other reason than it was posted quickly!) and that seems to be working just fine. Many Thanks, Ant |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
=countif ??? | Excel Discussion (Misc queries) | |||
How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions |