Excel Formula Help

[JasonMcConnell]

Can anybody help me with the following Excel Formula?

I have three columns
Reference Received Closed
EN990620 01/07/2003 16/07/2003
EN000344 01/07/2003 16/07/2003
EN000905 07/07/2003 30/07/2003

I want to create a formula that will count the number of reference numbers (Applications) that have been received in a given month.
e.g countif received date is > 1/07/2003 and received date <= 31/07/2003
I have tried doing this using countif but it ignores the second part of the formula?

Thanks for you help

 

[SkipVought]

Hi,

Take a look at SUMPRODUCT (i use named ranges)...
[tt]
=sumproduct((Reference="EN990620")*(Received>"1/07/2003")*(Received<="31/07/2003"))
[/tt]
Also PivotTable.

Skip,

_[red][/red]

 

[SkipVought]

Missed...
[tt]
=sumproduct(--(Reference="EN990620")*(Received>"1/07/2003")*(Received<="31/07/2003"))
[/tt]


Skip,

_[red][/red]

 

[xlbo]

Just use logic and 2 countif formulae...

=countif(Received,<= 31/07/2003)- countif(Received,<01/07/2003)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question