# Convert COUNTIFS for use in XL2003

##### Active Member
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*"))

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.
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

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

YES it is selected

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)

Excel 2010
ABCDE
131-Dec-161-Sep-1630-Nov-16
21-Sep-1630-Nov-16
322
422
2c
Cell Formulas
RangeFormula
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

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

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

Replies
2
Views
56
Replies
1
Views
68
Replies
4
Views
543
Replies
0
Views
75
Replies
3
Views
211

1,211,452
Messages
6,101,937
Members
447,764
Latest member
gopalgriffith

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back