Results 1 to 4 of 4

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

  1. #1

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

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

    Attached Files Attached Files

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    York, England
    Excel Version
    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.

    Last edited by Hercules1946; 2013-05-25 at 10:00 PM. Reason: TYping error

  3. #3
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Located near Abergavenny, South Wales, UK

    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.
    Attached Files Attached Files

    Roger Govier
    Microsoft Excel MVP

  4. #4
    Seeker k0st4din's Avatar
    Join Date
    Aug 2012
    BG, London
    It's very well done! Helped me!
    There is no greater pleasure than knowing you have an unknown friend who would have responded to help you when you need it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts