Convert COUNTIFS for use in XL2003

BradleyS

Active Member
Joined
Oct 28, 2006
Messages
301
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*"))
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

BradleyS

Active Member
Joined
Oct 28, 2006
Messages
301
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
13,886
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
13,886
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
4,574
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">31-Dec-16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1-Sep-16</td><td style="text-align: right;;">30-Nov-16</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1-Sep-16</td><td style="text-align: right;;">30-Nov-16</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:1.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">2c</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D1</th><td style="text-align:left">=EOMONTH(<font color="Blue">TODAY(<font color="Red"></font>),-4</font>)+1</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E1</th><td style="text-align:left">=EOMONTH(<font color="Blue">TODAY(<font color="Red"></font>),-1</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D2</th><td style="text-align:left">=DATE(<font color="Blue">YEAR(<font color="Red">A1</font>),MONTH(<font color="Red">A1</font>)-3,1</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E2</th><td style="text-align:left">=DATE(<font color="Blue">YEAR(<font color="Red">A1</font>),MONTH(<font color="Red">A1</font>),0</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B3</th><td style="text-align:left">=COUNTIFS(<font color="Blue">I:I,">="&EOMONTH(<font color="Red">TODAY(<font color="Green"></font>),-4</font>)+1,I:I,"<"&EOMONTH(<font color="Red">TODAY(<font color="Green"></font>),-1</font>)+1</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">I2:I100>=D1</font>),--(<font color="Red">I2:I100<E1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B4</th><td style="text-align:left">=COUNTIFS(<font color="Blue">I:I,">="&D2,I:I,"<"&E2</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C4</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">I2:I100>=D2</font>),--(<font color="Red">I2:I100<E2</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,269
Messages
5,600,633
Members
414,396
Latest member
rinianjell

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