Combining COUNTIF Criteria

[KMC]

SaleDate EstShipDate
7/2/08 7/18/08
7/12/08 8/5/08
7/30/08 9/4/08
8/3/08 8/25/08

How do I combine the functions below so I can see how many orders were
placed in July but will ship after July? I'm looking for a result of 2.

=COUNTIF(saledate,">=07/01/2008")-COUNTIF(saledate,">07/31/2008")
=COUNTIF(EstShipDate,">07/31/2008")

THANKS!!!

 

[John C]

Try this formula:

=SUMPRODUCT(--(Sales>=--"07/01/2008"),--(Sales<=--"07/31/2008"),--(Ship>--"07/31/2008"))

where Sales is your SalesDate range, and Ship is your ShipDate range.

 

[M Kan]

You can use a SUMPRODUCT function:

http://www.kan.org/tips/excel_sumproduct_advanced1.php

I think it would look something like:

=SUMPRODUCT(--(saledate>=07/01/2008),--(EstShipDate>07/31/2008))

I probably have the date format wrong, but this should count up your sales.
If you have a sales figure, that would be a 3rd argument to total up sales
within those ranges.

 

[M Kan]

Forgot your upper date range. This works

=SUMPRODUCT(--(SaleDate>=C11),--(SaleDate<=C12),--(EstShipDate>C12))

C11 = 7/1/08
C12 = 7/31/08

 

[KMC]

Thanks..this worked, but only if I define the cell range rather than using a
named range. How can I do the same with a named range or selected column
which contains label and blanks?