Lookup cell reference

[Christian411]

Hi guys,

I have a spreadsheet with a lot of columns. Column B contains date values against each row in the format 09-Apr-08.

I want to be able to use the COUNT function to count values in column C between 2 dates I specify. So for example, if I may want to count the number of numerical values in column C between say 01-Jan-08 and 09-Apr-08.

Is there a way I can maybe enter the 2 date values in seperate cells and have a formula come up with a corresponding range to perform the count function over? I was initially wondering whether I could do this by a vlookup but the column with the dates in is column B (i.e. not the left most column).

The other issue here is that the start and end dates may not necessarily correspond exactly to the numbers given above. For example, whilst I may be wanting to count the values between 01-Jan-08 and 09-Apr-08, the first value in column B in that range may be 04-Jan-08 so when establishing the range it would need to only approximately consider the dates I specify.

Hope someone can help!

Cheers,

Christian

[sweep]

Hi,

I think you could use a combination of the =MATCH and =OFFSET functions to achieve the desired result.

[Simon Lloyd]

You are looking at using SumIf used like this:
=SUMIF(B1:B24,"<="&D2,C1:C24)-SUMIF(B1:B24,"<"&D1,C1:C24)
where D2 is your latest date to look before and D1 is your earliest date to look after, the ranges i have used are for my entire range so i had entries between B1:B24 for dates and C1:C24 for things to count!

[robert111]

out of interest, I would have gone the route of a sumproduct table that calculated all "scores" above a certain date MINUS all scores above your second date.....

=sumproduct((a1:a1000>date1)*(b1:b1000)) - sumproduct((a1:a1000>date2)*(b1:b1000))

I find it easiest to put the 2 dates in specific cells and refer to those cells in the formula (where for ease and clarity I put date1 and date2)