Using OR in COUNTIFS

[Nefarious]

I've updated to 2007 and am trying to update a formula. Below is the formula I used in 2003 and the formula I'm trying to use in 2007 but it's not working. Any help would be appreciated.

2003 Formula:
=SUMPRODUCT(--(ProdLine="abc")+--(ProdLine="xyz"),--(ShipDate>DueDate))

2007 Formula:
=COUNTIFS(ProdLine,OR("abc","xyz"),ShipDate,"> DueDate")

I'm pretty sure my problem lies in my use of the OR function but I don't know how to get it to work.

All help is appreciated.

Thanks

 

[Trouttrap2]

I haven't tried your formula, but I do see that the underlined argument in =COUNTIFS(ProdLine,OR("abc","xyz"),ShipDate,"> DueDate")
should be written like ">"&DueDate)

 

[Aladin Akyurek]

I've updated to 2007 and am trying to update a formula. Below is the formula I used in 2003 and the formula I'm trying to use in 2007 but it's not working. Any help would be appreciated.

2003 Formula:
=SUMPRODUCT(--(ProdLine="abc")+--(ProdLine="xyz"),--(ShipDate>DueDate))

2007 Formula:
=COUNTIFS(ProdLine,OR("abc","xyz"),ShipDate,"> DueDate")

I'm pretty sure my problem lies in my use of the OR function but I don't know how to get it to work.

All help is appreciated.

Thanks

Your SumProduct formula is ill-formed, even if it works as intended.
It should run like:

=SUMPRODUCT((ProdLine="abc")+(ProdLine="xyz"),--(ShipDate>DueDate))

Even better:

=SUMPRODUCT(--ISNUMBER(MATCH(ProdLine,{"abc","xyz"},0)),--(ShipDate>DueDate))

On 2007 and later:

=SUM(COUNTIFS(ProdLine,{"abc","xyz"},ShipDate,">"&DueDate))

 

[Nefarious]

Thank you.. that worked perfectly.

 

[Dave Patton]

Excel Workbook
	A	B	C	D	E	F
1		Product	Ship Date	Due Date
2		ABC	Mar 10, 2011	Mar 01, 2011
3		ABC	Mar 10, 2011	Mar 01, 2011
4		EGG	Mar 10, 2011	Mar 01, 2011
5		EGG	Mar 10, 2011	Mar 01, 2011
6		XYZ	Mar 10, 2011	Mar 01, 2011
7
8			3	3	3	0
9
10	E8	=SUM(COUNTIFS(B2:B6,{"ABC","XYZ"},C2:C6,">"&D2))
11	F8	=SUM(COUNTIFS(B2:B6,{"ABC","XYZ"},C2:C6,">"&C2:D6))
12
1a

Excel 2003

Cell Formulas
Range	Formula
C8	=SUMPRODUCT(--((B2:B6="ABC")+(B2:B6="XYZ")),--(C2:C6>D2:D6))
D8	=SUMPRODUCT(--ISNUMBER(MATCH(B2:B6,{"abc","xyz"},0)),--(C2:C6>D2:D6))

Excel Workbook
Name	Refers To
z	=zList!$A$2
Workbook Defined Names

Nefarious

I quickly tried the above with Excel 2010.

I initially thought that there was a range of ship dates and a range of due dates.

I received correct answers with the SumProduct but I received correct answer with
Countifs only with a single DueDate.

Did I read your question incorrectly?

 

[Nefarious]

You are correct in your initial thought, my data is just as you displayed.