30, 60, 90 days late and due within 14 days
 

I am working on a spreadsheet that will calculate late suspenses. Basically
I have 3 different types of late suspenses and am looking to calculate
between 0 and 30 days late, 30 - 60 days late, 60 - 90 days late and over 90
days and each of these time frames for the three categories. Here is what my
spread sheet looks like:

Name ID TYPE SUSP DAYS OVER DUE UNIT
WOOD 6470 N/A 18-Mar-05 (234) A
SMITH 7453 UNIT 22-APR-05 (199)
B
JONES 9741 CO 1-Nov-05 (6)
C

Here is the formula I am trying to use:
=SUMPRODUCT(--(DATA!C2:C319="UNIT"),--(DATA!D2:D319=TODAY()+30))

30, 60, 90 days late and due within 14 days
 

Are you trying to count how many in each band? If so, then try

=SUMPRODUCT(--(DATA!C2:C319="UNIT"),--(DATA!D2:D319<(TODAY()-90)))

=SUMPRODUCT(--(DATA!C2:C319="UNIT"),--(DATA!D2:D319=(TODAY()-90))), --(DATA
!D2:D319<(TODAY()-60)))

etc.


--

HTH

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


"armyaviatr" wrote in message
...
I am working on a spreadsheet that will calculate late suspenses.
Basically
I have 3 different types of late suspenses and am looking to calculate
between 0 and 30 days late, 30 - 60 days late, 60 - 90 days late and over
90
days and each of these time frames for the three categories. Here is what
my
spread sheet looks like:

Name ID TYPE SUSP DAYS OVER DUE UNIT
WOOD 6470 N/A 18-Mar-05 (234) A
SMITH 7453 UNIT 22-APR-05 (199)
B
JONES 9741 CO 1-Nov-05 (6)
C

Here is the formula I am trying to use:
=SUMPRODUCT(--(DATA!C2:C319="UNIT"),--(DATA!D2:D319=TODAY()+30))