CountIf X & within a date range

Stresbringer

Board Regular
Joined
Mar 16, 2006
Messages
84
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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

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