Countif Problem

[rmtimmah]

Hi, I have a sheet that contains dates going back a few years. I am trying to use the countif function to count the different sites in column B according to date. eg I want to be able to find out how many jobs for PHO1 were created between todays date and 7 days ago, this can go into column C, In column D I need to find out how many jobs were created by PHO1 between 7 and 14 days ago etc etc.

Book2.xls
	A	B	C	D
1	26/07/2007	PHO1
2	26/07/2007	PHO1
3	26/07/2007	ISA1
4	26/07/2007	CC01
5	26/07/2007	CC01
6	26/07/2007	CC01
7	27/07/2007	CC01
Sheet1

 

[ExcelChampion]

=SUMPRODUCT(--($B$1:$B$100 = "PHO1"),--($A$1:$A$100 >=today()-7),--($A$1:$A$100 <=today()-14))

How is this?

 

[rmtimmah]

That didn't work because of the absolute values but I have managed to figure it out by using another cell with a value of =today()-14 and referencing the working formula to that.

Thanks anyway

 

[Glove_Man]

Watch the direction of the <..... That is probably why Excel Champion's formula is returning zeros......