Convert COUNTIFS for use in XL2003

BradleyS

Active Member
Joined
Oct 28, 2006
Messages
316
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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

BradleyS

Active Member
Joined
Oct 28, 2006
Messages
316
Office Version
  1. 2010
Platform
  1. Windows
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:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,460
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
First question is do you have the analysis toolpak installed on your Excel 2003?
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,460
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

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

Well-known Member
Joined
Feb 15, 2002
Messages
5,194
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

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.
 

BradleyS

Active Member
Joined
Oct 28, 2006
Messages
316
Office Version
  1. 2010
Platform
  1. Windows
Restricting the range did it. Thank you. I will use a DNR instead
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,951
Messages
5,834,535
Members
430,295
Latest member
amdis

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
Top