Convert COUNTIFS for use in XL2003

[BradleyS]

I have 2 formula's that work fine in XL2010, but not in XL2003, I figured out that COUNTIFS are the problem.
Could someone help me convert them so I can use them in both versions of XL

This is one of the XL2010 codes:
=COUNTIFS(CXPATS!I:I,">="&EOMONTH(TODAY(),-4)+1,CXPATS!I:I,"<"&EOMONTH(TODAY(),-1)+1)
Below is one of my many attempts using SUMPRODUCT, but they either don't work or continually show #NUM!

=SUMPRODUCT(--(CXPATS!I:I>=EOMONTH(TODAY(),-4)+1,--(CXPATS!I:I<EOMONTH(TODAY(),-1)+1))


This is the other XL2010 code: {=SUM(COUNTIFS(CXPATS!L:L,{"6859*","685A*"}))}
...and one of my many attempts using SUMPRODUCT again, but again I keep getting #NUM! on most attempts

=SUMPRODUCT(--(CXPATS!L:L="6859*"),--(CXPATS!L:L="685A*"))

 

[BradleyS]

I have figured out the 2nd now by adding 2 COUNTIF together =COUNTIF(CXPATS!L:L,"6859*")+(COUNTIF(CXPATS!L:L,"685A*")) but still struggling with the first

 

[MARK858]

First question is do you have the analysis toolpak installed on your Excel 2003?

 

[BradleyS]

YES it is selected

 

[MARK858]

2nd thing is to restrict the range in Sumproduct, do not use entire columns in earlier versions (in fact don't use them in later versions but for other reasons)

 

[Dave Patton]

Excel 2010
	A	B	C	D	E
1	31-Dec-16			1-Sep-16	30-Nov-16
2				1-Sep-16	30-Nov-16
3		2	2
4		2	2
2c

Cell Formulas
Range	Formula
D1	=EOMONTH(TODAY(),-4)+1
D2	=DATE(YEAR(A1),MONTH(A1)-3,1)
E1	=EOMONTH(TODAY(),-1)
E2	=DATE(YEAR(A1),MONTH(A1),0)
B3	=COUNTIFS(I:I,">="&EOMONTH(TODAY(),-4)+1,I:I,"<"&EOMONTH(TODAY(),-1)+1)
B4	=COUNTIFS(I:I,">="&D2,I:I,"<"&E2)
C3	=SUMPRODUCT(--(I2:I100>=D1),--(I2:I100))
C4	=SUMPRODUCT(--(I2:I100>=D2),--(I2:I100))

N.B.
Edit the suggestion to include relevant ranges
- do not use entire columns
- add the sheet names

You can build the date criteria into the formulas if you want to.

 

[BradleyS]

Restricting the range did it. Thank you. I will use a DNR instead