Count occurances of date across multiple columns & display # of occurances for each column

I have spreadsheets made for different projects and each project is split up into columns that indicate when each step of that project is completed. When a step is completed I mark the date of completion. The projects can be repeated for many clients at once therefore, the number of times each step is completed (i.e. 5 step ones completed) becomes summed using the count feature and then multiplied by a price for that step. This allows me to know how much money I'm making from each step.

I created an additional worksheet (fiscal year 2012) that needs to be able to count each step completed of each project and display the amount earned for a particular month for a particular step. For example, "how much did I earn total for step 1 for the month of January 2012". Then determine "how much I earned for step 2 for the month of January 2012". This would repeat for all months and all steps of each project. My goal is to breakdown my revenue by month to forecast future earnings.

So, I want to create one "master" financial spreadsheet that will pull the data from all my other project spreadsheets. I do not plan on renaming or moving the files so my master will not loose the reference.

I have found a way to do this, but the function coding will be very long. I used a sumproduct array:
=(SUMPRODUCT(--('1-1-11 to 4-30-12'!$F$9:$F$25>=DATE(2011,7,1)+0),--('1-1-11 to 4-30-12'!$F$9:$F$25<=DATE(2011,7,31)+0))*'1-1-11 to 4-30-12'!$F$8)

This allowed me to search only for the month of July dates in a particular column. I would have to repeat this code for every column and for every month. There has to be an easier method.

Any suggestions and help is greatly appreciated. Thank you in advance!

I have attached an example of a project worksheet.

I use this method in lots of reports but I refer to the date in the column heading instead of hard coding in the formula - see attached.

You can use the fill handle to replicate your formula to the right and down. This will adjust any cell references which don't have a $ sign in front - e.g., C$4 will change to D$4, E$4, etc as you drag to the right.

If your new data is in separate worksheets you can select the appropriate rows in the summary worksheet and replace 1-1-11 to 4-30-12 with the new tab name. Be sure to retain the single quotes around the tab name - 'New Tab Name' - or the formulas won't work. This replacement method assumes that all of the worksheets have the data in the same columns and rows.

I don't know of an easier way of doing this unless you get into VB.

Pl see the attached file.
I have used "Opera Projection(2)" Sheet.Original Sheet not altered.

I have used Spare cells range AE10:AT26 (With formula ) to simplify the long formulas given by you.

The required formulas are available in AE34 to AH39.
You can drag each Formula Downwords only.

I feel this is what you require.

With Regards ,
kvsrinivasamurthy