Combining COUNTIF Criteria
 

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!!!

Combining COUNTIF Criteria
 

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.
--
John C


"KMC" wrote:

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!!!

Combining COUNTIF Criteria
 

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),--(EstShipDate07/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.

--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"KMC" wrote:

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!!!

Combining COUNTIF Criteria
 

Forgot your upper date range. This works

=SUMPRODUCT(--(SaleDate=C11),--(SaleDate<=C12),--(EstShipDateC12))

C11 = 7/1/08
C12 = 7/31/08
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"KMC" wrote:

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!!!

Combining COUNTIF Criteria
 

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?

"Chip Pearson" wrote:

Use the following array formula:

=SUM(1*IF(MONTH(A1:A10)=7,MONTH(B1:B10)7))

where A1:A10 are the sales dates and B1:B10 are the ship dates.

Since this is an array formula, you must press CTRL SHIFT ENTER rather than
just ENTER when you first enter the formula and whenever you edit it later.
If you do this properly, Excel will display the formula enclosed in curly
braces { }. See http://www.cpearson.com/Excel/ArrayFormulas.aspx for more
details about array formulas.

--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)





"KMC" wrote in message
...
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!!!