Convert COUNTIFS for use in XL2003

BradleyS

Active Member
Joined
Oct 28, 2006
Messages
328
Office Version
  1. 2010
Platform
  1. Windows
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:
Upvote 0
First question is do you have the analysis toolpak installed on your Excel 2003?
 
Upvote 0
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)
 
Upvote 0

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
- add the sheet names

You can build the date criteria into the formulas if you want to.
 
Upvote 0
Restricting the range did it. Thank you. I will use a DNR instead
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top