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

    Thanks,
    Attached Files Attached Files

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    766
    Articles
    0
    Excel Version
    2010
    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
    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
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    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.
    Attached Files Attached Files
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

  4. #4
    Seeker k0st4din's Avatar
    Join Date
    Aug 2012
    Location
    BG, London
    Posts
    6
    Articles
    0
    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
  •