I'll assume that, in Sheet1, the data start in row 2 while row 1 holds appropriate labels descriptive of data underneath like Dates, etc.

On Sheet2:

In A1 you have a criterion date.

In B1 enter: =MATCH(9.99999999999999E+307,Sheet1!A:A)-1

Note. The -1 reflects the fact that the actual data start in row 2 of Sheet1.

In A2 enter: TypeA

In A3 enter: TypeB

etc.

In B2 enter: =SUMPRODUCT((OFFSET(Sheet1!A$2,0,0,$B$1,1)>$A$1)*(OFFSET(Sheet1!B$2,0,0,$B$1,1)=$A2))[ copy down this as far as needed ]

Note that this formula can cope automatically with changes to the data in A:B in Sheet1. If the data ranges are fixed (do not change or change seldom), you can delete the formula in B1 and use in B2 a formula with fixed ranges like:

=SUMPRODUCT((Sheet1!A2:A18>$A$1)*(Sheet1!B2:B18=$A2))

The slight problem was my definition of what I wanted to do. I said count if the date was greater than my criterion date, whereas what I really need is greater than _or equal to_ that date

Lewis: That's not a big deal. Just change the

>$A$1

bit to:

>=$A$1

Thus:

=SUMPRODUCT((OFFSET(Sheet1!A$2,0,0,$B$1,1)>=$A$1)*(OFFSET(Sheet1!B$2,0,0,$B$1,1)=$A2))

=SUMPRODUCT((Sheet1!A2:A18>=$A$1)*(Sheet1!B2:B18=$A2))

Aladin

==========

Thanks but can you explain something

Thanks but can you explain something (2nd time lucky)

What does =MATCH(9.99999999999999E+307,Sheet1!A:A)-1

do as with my data I get a value of 34 whereas there are in fact only 32 entries in the column?

Re: Thanks but can you explain something (2nd time lucky)

Match isn't counting how many entries you have in A. Rather it returns the number of the row holding the last date entry (it's the 35th row in your cse). The 32 entries that you mention suggest that you have blank cells in the range of interest. But that's OK. The -1 part is related to my assumption that the real data (exluding the labels)start at row 2. This assumption is also the reason why the OFFSET bits in the formulas have as their first arg Sheet1!$A$2, etc.

========