Total of differing dates
 

I have one column with a list of dates that show when orders are or were
expected to ship and one column with a list of dates that show when those
orders did actually ship. What I would like to do is total how many of these
orders shipped prior to, on time or after the expected ship date. I don't
know how to set up this formula. Thanks, Doug

Expected dates in A2:A100, defacto dates in B2:B100
orders shipped prior to expected if all the ranges are filled with dates and
real excel dates (numeric)


=SUMPRODUCT(--(A2:A100B2:B100))

if blanks are involved as well

=SUMPRODUCT(--(A2:A100B2:B100),--(ISNUMBER(A2:A100)),--(ISNUMBER(B2:B100)))

on time

=SUMPRODUCT(--(A2:A100=B2:B100))

or


=SUMPRODUCT(--(A2:A100=B2:B100),--(ISNUMBER(A2:A100)),--(ISNUMBER(B2:B100)))

late

=SUMPRODUCT(--(A2:A100<B2:B100))

or

=SUMPRODUCT(--(A2:A100<B2:B100),--(ISNUMBER(A2:A100)),--(ISNUMBER(B2:B100)))


Regards,

Peo Sjoblom




"parkerlex" wrote:

I have one column with a list of dates that show when orders are or were
expected to ship and one column with a list of dates that show when those
orders did actually ship. What I would like to do is total how many of these
orders shipped prior to, on time or after the expected ship date. I don't
know how to set up this formula. Thanks, Doug