Help with Count function

[bazz]

Hi All, I have a table along these lines with a DateID column and a Value column (both named ranges catering for 1000 rows) and I want to get the count of Values for each Date ID in Count column like this :

DateID...Value......Count
1.............3...........3
1.............6...........3
1.............8...........3
2.............4...........2
2.............5...........2
3.............7...........3
3.............11.........3
3............(Is Null)...3
3............(Is Null)...3
3............(Is Null)...3
3.............5...........3

I thought I had a workaround using an array formula but discovered that an abundance of null values in any DateID group produced spurious results.

Thanks for any help,
Barry
(Excel 2007)

 

[Andrew Poulsom]

Try eg:

=SUMPRODUCT(--(A$2:A$12=A2),--ISNUMBER(B$2:B$12))

 

[bazz]

Perfecto! Many thanks Andrew.