Counting

[Guest]

Hi there I have a spreadsheet that is set up as follows;


Type Age From To (blank to date is case
still open)
John 1 12 1/4/05
Jackie 2 15 1/5/05 7/8/05
Ian 3 16 1/4/05 8/4/05
Mike 3 17 7/6/05 9/9/05
Luke 3 17 1/6/05
Vince 1 12 2/5/05 8/5/05
Tina 1 18 1/4/05

I want to populate the following table

Type1 Type2 Type3
Over 16
Under 16

So effectively I want to know how many cases of the type and age are open
currrently. So far example if we were in June the table would look like

Type1 Type2 Type3
Over 16 1 2
Under 16 1

All help is greatly appreciated

J

 

[Bob Phillips]

Assuming the source table is in A1:E100 and the target table is H1:K3, then
use a formula of

=SUMPRODUCT(--($B$2:$B$10=1),--($C$2:$C$10>16),--(MONTH($D$2:$D$10)<=6))

for over 16 type 1. Adjust the rest to suit

--

HTH

RP
(remove nothere from the email address if mailing direct)

Hi there I have a spreadsheet that is set up as follows;

Type Age From To (blank to date is case