Total of differing dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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:A100>B2:B100))

if blanks are involved as well

=SUMPRODUCT(--(A2:A100>B2: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
 

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

Similar Threads

Need Formula For Data Summary 7
PIVOT TABLES-PERCENT/AMOUNT OF CHANGE 3
Data Validation for dates 5
IF function 3
If statement or lookup statement not sure 2
Conditional formatting-Date range 1
How do I move entire row from one excel sheet to another 3
Not sure if this is possible... 2

Back
Top