VBA to find cell value in column and count occurances in same row within date range

Hi,

I would like to design a macro that can find a value within a cell in a colunm and copy a value in the row then count occurances of that value next time a match in the colunm is found on a different row, but also do this only if the date range in the row is within a specified range. So a multi-critieria search and copy operation.

To explain a little better here is a simplified example of my spread sheet that is in input for the macro.

	A	B	C	D	E
1	PART NO.	date in	date out	comp1	comp2
2	12-3	01-12-13	05-12-13	R1
3	00-1	01-12-13	05-12-13	X1	Z1
4	12-3	03-12-13	06-12-13	R1	C3
5	00-2	05-12-13	06-12-13	L2
6	00-2	07-12-13	10-12-13	L3
7	12-3	05-01-14	08-01-14	R2	C3
8	00-1	06-01-14	10-01-14	X1	Z1
9	00-1	06-01-14	10-01-14	X2	Z2
10	12-3	10-01-14	15-01-14	C3	R1

*some component fields will be blank in normal use.

So what I want to do is to search by "PART NO." within a specific date range and count the occurances of components, and output something like this.

So for date range from 01-01-14 >> to 01-02-14 serching part "12-3" the table on a new sheet would look something like this.

	A	B
1	12-3
2	R1	1
3	R2	1
4	C3	2

Column A lists the coponents found on the same row as the seach critria, in this example "12-3".
Column B lists the number of occurances of the components. This search is limited to a date range input by the user.

So what I can see is the components replaced and the amount of times replaced on a specific item over a fixed time.

*It is difficult to describe this problem without showing the macro output, but this is just an example for the purpose of explanation.

I know a bit of VBA programming and initially I started with an input box that asks for the part number and limit dates. Any help is greatly appreciated. I know someone can probably solve this in a 100th of the time it would take me.

P.S I couldn't attach the .xlsx file for some reason.

I'm already using countifs to count components per part, I haven't tied dates into it yet, however my formulas are so long and so many that they slow down excel everytime you enter a value into a cell covered by the countifs

I'm using Excel 2007.

So I thought a macro approach might help get around that.

=COUNTIFS(sheet1!Q:Q,q_1,sheet1!U:U,A176)+COUNTIFS(sheet1!R:R,q_1,sheet1!U:U,A176)+COUNTIFS(sheet1!S:S,q_1,sheet1!U:U,A176)+COUNTIFS(sheet1!T:T,q_1,sheet1!U:U,A176)+COUNTIFS(sheet1!X:X,q_1,sheet1!AB:AB,A176)+COUNTIFS(sheet1!Y:Y,q_1,sheet1!AB:AB,A176)+COUNTIFS(sheet1!Z:Z,q_1,sheet1!AB:AB,A176)+COUNTIFS(sheet1!AA:AA,q_1,sheet1!AB:AB,A176)+COUNTIFS(Ndiag!Q:Q,q_1,Ndiag!B:B,A176)+COUNTIFS(Ndiag!R:R,q_1,Ndiag!B:B,A176)+COUNTIFS(Ndiag!S:S,q_1,Ndiag!B:B,A176)+COUNTIFS(Ndiag!T:T,q_1,Ndiag!B:B,A176)

In this case, q_1 is a cell name of a cell containing "Q1". the formula searches 12 columns, 8 in the first sheet and 4 in the 2nd, then adds all the countifs. hence it's lenght. A load of these slow down excel a bit :P