Counting Question

I

ingalla

Can anyone tell me how to count the following. I have a list containing
date, description and type.

I need to count all of the fields containg "Type A" starting on the 05/06/06
and finishing a week later.

How do i put this into a formula.

Thanks in advance
 
A

Ardus Petus

Say your Date/Description/Type array is in A2:C23

Say wanted start date is in E2, wanted type in F2.

To count matching entries, enter:
=SUMPRODUCT((A2:A22>=E2)*(A2:A22<=E2+7)*(C2:C22=F2))

See example: http://cjoint.com/?gcnviozPkd

HTH
 
I

ingalla

Ardus

Thanks very much for your help, it works a treat. Can you, or anyone else,
tell me one thing.

As this data is going to be constantly expanding, how can i include in your
formula, a way of setting the cell, that is now A22 in your formula, to the
last cell of the data using the formulas?

I need A22 to update to the last cell of data in the column each time the
workbook is opened and updated?

Thanks

Andy
 
A

Ardus Petus

To be on the safe side, you can type:
=SUMPRODUCT((A2:A65536>=E2)*(A2:A65536<=E2+7)*(C2:C65536=F2))

A dynamic range would be costlier than searching 65536 rows

HTH
 
Top