Formula to count entries as per month, year and submission wise from different sheets

Hi Everyone,

Could anyone help me with generating a formula to calculate the total submissions?

I have the excel sheet attached and the answers on the sheet 2.

Could some one help me with generating formula for first two table?

Total submission=Detailed Plan + Detailed Plan & Estidama (from sheet CS-CP) + Site Mobilization (from Sheet Mobilization) + DOT (from Sheet DOT) submitted for the year 2013 and month Jan, FEb and March

Thanks,

Hello
Ive had a look at your formulae highlighted yellow, and I noticed some problems:
(1) You only have coding in your formulae for the CS-CP Sheet, so there is no count of the submissions in the other two data sheets.
(2) The way you are checking the dates is flawed. Based on B8 to D11 (Sheet 2) Im assuming that you want a count for 2013 only, but in all the formula cells except J9 your matching equal on the month only so that the count will include the 1st qtr of 2012. In J9, you are matching equal on dates, but one is formatted "MMM" and the other "MMM YY" so they will all return FALSE with a count of 0.

If you can resolve these issues your results should be OK.

HTH

Hi

Provided you can accept a solution that includes a small amount of VBA then take a look at the attached file.
I added an extra column called Source to each of your three sheets, and populated each row with the name of the sheet.
I created anew sheet called Alldata.
With VBA code, I populated this sheet called Alldata with the data from each of your other 3 sheets.
I created a Pivot Table based upon this amalgamated data, to perform the calculations required for your summary.

I then populated the yellow cells in your sheet 2 with the results using the GetPivotData function.

i leave it to you to populate the other tables.

The easiest way is to place you cursor inside the cell where you want the result, enter = and then point to the cell(s) on the PT whose data you want to show.

It's very well done! Helped me!