# CountIf X & within a date range

#### Stresbringer

##### Board Regular
Hi Guys

I have a spreadsheet which contains a Summary sheet & 12 Monthly data sheets in each sheet column H is a named range labeled Jan - Dec etc

On the Summary sheet I have a formulas which count The critera which appear in these ranges

Ie CountIF(JAN,\$A40)
CountIF(FEB,A40)....

What i need to do is have one annual data sheet rather than 12 and i need a formula which counts the critera as above but only counts it when it falls within a certain date range ..ie Jan Feb mar etc

I plan to have annual sheets which will be labled 2008, 2009 etc

Ie
Count if x and is between 01/01/08 - 31/01/08
Count if x and is between 01/02/08 - 28/02/08

Can anyone help?

### 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.

#### schielrn

##### Well-known Member
If you are using pre-2007, try this:

=sumproduct(--(XRange="x"),--(1stDateRange > ="01/01/08"*1),--(2ndDateRange < ="31/01/08"*1))

If you are using 2007 use countifs. I wasn't sure about your ranges exactly so fill in with the appropriate named ranges. And make sure you are not using whole columns for ranges and that they are the same length.

Hope that helps.

Replies
4
Views
227
Replies
7
Views
2K
Replies
13
Views
765
Replies
0
Views
600
Replies
2
Views
223

1,191,191
Messages
5,985,215
Members
439,947
Latest member
fabiannic

### 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