Counting

[Austin]

Hi everyone,

I need some help on something that I have been asked to do
but am finding difficult.
I need to count the number of different cases in every
month, I can count the ones that have start and end dates
but find it immpossible to count the ones that have no end
date (cos they are 0) meaning that they are open.
I have tried to use an array incorporating the OR function
but can't get it to work.
Below is an example of what I am trying to achieve.


Count table based on data below.

1/4/04 1/5/04 1/6/04 1/7/04 1/8/04 1/9/04
31/4/04 31/5/04 30/6/04 31/7/04 31/8/04 30/9/04

Case1 1 1 1 1 2 1
Case2 0 1 1 2 2 1
Case3 1 1 1 1 1 1
Case4 1 1 1 1 2 2



Cases - Sample Data
Start End
Case1 1/4/04 1/8/04
Case2 31/5/04
Case1 1/8/04
Case3 31/4/04
Case2 1/7/04 18/9/04
Case4 1/4/04
Case4 1/8/04

Can anyone help me?


Regards

Austin

 

[Bernie Deitrick]

Austin,

With your table "Sample Data" in A1:C8 (labels in row 1, case names in
A2:A8, start dates in B2:B8, and end dates or blanks in C2:C8 .

And with:
your 'bin' starting dates in B11:G11
your 'bin' ending dates in B12:G12
you case names in A13:A16

Enter this formula in B13

=SUMPRODUCT(($B$2:$B$8<=B$12)*($A$2:$A$8=$A13))-
SUMPRODUCT(($C$2:$C$8<>"")*($C$2:$C$8<B$11)*($A$2:$A$8=$A13))

and copy to B13:G16.

One difference from your desired table is that this returns a 2 for
September for case2, not 1. This seems to be the correct value, so your
example table seems incorrect.

HTH,
Bernie
MS Excel MVP