Counting


A

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
 
Ad

Advertisements

B

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top