+ Reply to Thread
Results 1 to 17 of 17

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

  1. #1
    Registered User
    Join Date
    05-24-2013
    Location
    Abu Dhabi, UAE
    MS-Off Ver
    Excel 2007
    Posts
    21

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

    Hi there,

    Could anyone be able to help me with the attached file?

    I need to count the entries of the documents monthly wise as per the year(submission date) against document name and the KPI.



    I have the answers expected on Sheet 2.

    Thanks,
    Attached Files Attached Files

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,766

    Re: Formula to count entries as per month, year and submission wise

    In the earlier suggested formula's change these...

    "MMM"

    to

    "MMM 'YY"

    Remove this... from the suggested formula's
    LEFT(TRIM(B$8),3)

    Please do the correction to all the suggested formula's
    Last edited by :) Sixthsense :); 05-25-2013 at 02:51 AM. Reason: Added Link Of Another Thread

  3. #3
    Registered User
    Join Date
    05-24-2013
    Location
    Abu Dhabi, UAE
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Formula to count entries as per month, year and submission wise

    I tried doing it but it gives me a wrong value. More over could you look at the first table on Sheet 2, i need the total entry from all the sheets. how would i do that?

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,766

    Re: Formula to count entries as per month, year and submission wise

    Sorry to say that I can't..... I can just tell you how to do it and I can't apply all the formula in your file for each and every time continuous change in your data structure.

    Please try to do it on your own... Or i hope someone will do it for you..........

  5. #5
    Registered User
    Join Date
    05-24-2013
    Location
    Abu Dhabi, UAE
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Formula to count entries as per month, year and submission wise

    i tried modifying the formula but could not figure what went wrong. Anyway sorry sixthsense to bother you. Thanks for the help.

  6. #6
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,766

    Re: Formula to count entries as per month, year and submission wise

    This is how the B9 cell formula will be...

    =SUMPRODUCT(('CS-CP'!$B$9:$B$501={"Detailed Plan","Detailed Plan & Estidama"})*(TEXT('CS-CP'!$C$9:$C$501,"MMM 'YY")=TRIM(B$8))*ISNUMBER('CS-CP'!$C$9:$C$501))

    try to change the rest on your own...

  7. #7
    Registered User
    Join Date
    05-24-2013
    Location
    Abu Dhabi, UAE
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Formula to count entries as per month, year and submission wise

    it did not work but thanks for the reply sixthsense.

  8. #8
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Formula to count entries as per month, year and submission wise

    Try this in B9 :
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    drag across as needed
    (Note 1- they values in your table are wrong, there are only 7 Jan 13 submissions, not 9)
    (Note 3 - I have put the formula into Columns N-O, but the formula works in B9-D
    9 as well)
    (note 3 - The trim is there to remove leading/Trailing spaces (which your headers do have, if you edited those out, you could remove the trim functions )
    See attached

    Hope this helps

    EDIT -
    not sure what is going on in the next rows, so did not supply formulas for them yet
    Attached Files Attached Files
    Last edited by dredwolf; 05-25-2013 at 04:18 AM. Reason: Spelling
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  9. #9
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Formula to count entries as per month, year and submission wise

    If you highlight the cells that are out of KPI range so we ca see what should appear, we may be able to offer you a solution...as is stands, your sample shows two extra counts for everything ( there are only 20 2013 data sets..yet all your samples seem to indicate 22 data sets....not sure what is going on...

  10. #10
    Registered User
    Join Date
    05-24-2013
    Location
    Abu Dhabi, UAE
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Formula to count entries as per month, year and submission wise

    Dredwolf,

    Thanks for the reply. B9=B17+B24+B31 and its the same with C9 which is C9=C17+C24+1 and D9=D17+D24+D31.

    I need the formula for B17, B18 and B19.

    As advised by Sixthsense earlier below were the formula which would calculate only for Jan 2013 but now there are few enttries for Jan2012 and i need to be separated.

    B17 is the entries from CS-CP sheet which contains only Detailed Plan and Detailed Plan & Estidama
    =SUMPRODUCT((Sheet1!$B$8:$B$500={"Detailed Plan","Detailed Plan & Estidama"})*(TEXT(Sheet1!$C$8:$C$500,"MMM")=LEFT(TRIM(B$8),3))*ISNUMBER(Sheet1!$C$8:$C$500))

    B18 is the entries from CS-CP sheet which contains only detailed plan and detailed plan & estidama but KPI (Row E >=-1)
    =SUMPRODUCT((Sheet1!$B$8:$B$500={"Detailed Plan","Detailed Plan & Estidama"})*(TEXT(Sheet1!$C$8:$C$500,"MMM")=LEFT(TRIM(B$8),3))*(Sheet1!$E$8:$E$500<-1))

    B19 is the entries from CS-CP sheet which contains only detailed plan and detailed plan & estidama but KPI (Row E <=-1)
    =B17-B18


    Hope you understood what my query is?

    Please let me know if you need more clarifications... Thanks a lot

  11. #11
    Registered User
    Join Date
    05-24-2013
    Location
    Abu Dhabi, UAE
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Formula to count entries as per month, year and submission wise

    i made a mistake, Sheet! is referred to "CS-CP"

  12. #12
    Registered User
    Join Date
    05-24-2013
    Location
    Abu Dhabi, UAE
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Formula to count entries as per month, year and submission wise

    I have attached the sheet again with color codes hope this will make you easy to understand what my query is

    thanks,
    Attached Files Attached Files

  13. #13
    Forum Contributor
    Join Date
    09-11-2009
    Location
    Paris
    MS-Off Ver
    Excel 2003
    Posts
    102

    Re: Formula to count entries as per month, year and submission wise

    Copy of Dashboard.xlsHi,
    See attached file for your reference!

    Quote Originally Posted by hanidean View Post
    I have attached the sheet again with color codes hope this will make you easy to understand what my query is

    thanks,

  14. #14
    Registered User
    Join Date
    05-24-2013
    Location
    Abu Dhabi, UAE
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Formula to count entries as per month, year and submission wise

    Thanks a ton Duanzhuanming

    You are the best!

    Thanks to Sixthsense and dredwolf

  15. #15
    Registered User
    Join Date
    05-24-2013
    Location
    Abu Dhabi, UAE
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Formula to count entries as per month, year and submission wise

    Duanzhuanming,

    I have one question. You used =SUMPRODUCT formula for all Cells. except J30, K30, L30 you used =+SUMPRODUCT

    May i know what the difference?

    Thanks,

  16. #16
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,766

    Re: Formula to count entries as per month, year and submission wise

    Quote Originally Posted by hanidean View Post
    You used =SUMPRODUCT formula for all Cells. except J30, K30, L30 you used =+SUMPRODUCT, May i know what the difference?
    Please Refer the below link

    http://www.xldynamic.com/source/xld.SUMPRODUCT.html

  17. #17
    Registered User
    Join Date
    05-24-2013
    Location
    Abu Dhabi, UAE
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Formula to count entries as per month, year and submission wise

    Thank you for the information Sixthsense

    Thank you everyone in the forum for being very helpful.

    Much appreciated.

    Cheers!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1