COUNTIFS Dates

[JCSR0000]

I'm trying to generate a report showing criteria of a Breakdown for each week.
I want to count the number for each 7 day period with multiple criteria. I want it to only count thats weeks data.

WE</SPAN>	Total</SPAN>	</SPAN>	</SPAN>	</SPAN>
April 6, 2013</SPAN>	22</SPAN>
April 13, 2013</SPAN>	7</SPAN>
April 20, 2013</SPAN>	13</SPAN>
April 27, 2013</SPAN>	0</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL span=3></COLGROUP>

 

[Akashwani]

Hi,

In the absence of any sample data or range information provided, is this what you want to achieve?...

Excel Workbook
	A	B	C	D	E	F	G	H
1	Date		WE	Total		Row Labels	Count of Date
2	01/04/2013		06/04/2013	6		31/03/2013 - 06/04/2013	6
3	02/04/2013		13/04/2013	7		07/04/2013 - 13/04/2013	7
4	03/04/2013		20/04/2013	4		14/04/2013 - 18/04/2013	4
5	04/04/2013		27/04/2013	0		Grand Total	17
6	05/04/2013
7	06/04/2013
8	07/04/2013
9	08/04/2013
10	09/04/2013
11	10/04/2013
12	11/04/2013
13	12/04/2013
14	13/04/2013
15	14/04/2013
16	15/04/2013
17	16/04/2013
18	17/04/2013
19
Sheet1

The formula in C2 need to be copied down and the cell references changed to suit your layout.
Alternatively, you could use a Pivot Table (F1:G5) and Group your data by 7 days.
To create a Pivot Table, select A2, press ALT N V T, click Existing Worksheet, click in the white box, click cell F1, click OK.
Drag Date to the Row Field, Drag Date to Values Field.
Click the first line, click Group Field on the Options Tab, change the Start date to 31/03/2013, click Days and change Number of days to 7, click OK.

I hope that helps.

Ak

 

[Akashwani]

Hi,

"The formula in C2 need to be copied down and the cell references changed to suit your layout."

That should be, the formula in D2! Duh!!

Ak