Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Help with formula (again)

    Hi All

    Need some of you expert help - once again.

    Please see the attached sample. I have a worksheet that is a download from data base.

    In the tab called "Total" you can see the results I'm trying to achieve. I'm sure that I've done this before, but just can't seem to get it this time.

    I need to count the total of records in the worksheet that occurred in a particular month (1) and then the total per Project number in a particular month (2).

    I hope that makes sense, any assistance/solutions would be greatly appreciated.

    Thanks
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Verada

    see attached file. I kept this in Excel2003 format.

    I simplified the formulas by naming two ranges as colB and colC for the Entry Date and Project Number data ranges.
    I also extended the 'month range' in row8 of sheet [Totals] by adding cells [F8] and [T8] (these could be hidden if desired).

    Then it was easy to use array formulas for getting the required values.

    NOTE: Your Project Numbers have a trailing space. So I made sure there was a trailing space in the Project Numbers on sheet [Totals].

    Another way of doing this is to use a pivot table.

    zeddy
    Attached Files Attached Files

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks zeddy

    I actually have Excel 2010, not sure why it looks like 2003. Will Excel 2010 change the way the formula works or provide an alternative option?

    I've reduced the name of the project numbers-normally they have a name after the number.

    Thanks

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Verada

    ..it will work the same in all later versions, so same in Excel2010

    zeddy

  5. #5
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Hi zeddy,

    I tested your formulas this morning and looks pretty close, however it looks to be counting some extra values, ie when you apply a filter on the worksheet for records in Jan 2014, it returns at total of 7, However the formula returns 11 for the month (totals in row 9) and 9 when you total the values by project number and month (Jan-14).

    Any thoughts or other suggestions anyone?

    Thanks

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Verada,

    The problem is with the DateValue in F8 on Totals sheet which is 12/1/2013! Change it to 12/31/2013 and you will get the correct count.
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thank RG

    I changed the date as you suggested, that fixed the Jan-14 issue, however there are still variations as the range progresses (based on rz-verada-1.xls)

    Total 7 14 17 22 22 20 19 16 10 8 14 11 4
    Total by project 0 0 0 0 0 0 0 0 -1 -2 -5 -8 -5

    Also when you filter Jan-15 it only return one record where calculated total return 9 and the project total return 4 (differential of -5)

    Any other thoughts?

    Regards
    Last edited by verada; 2015-02-12 at 21:14. Reason: Looks like the number loose alignment in the post

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Verada,

    The Jan-15 problem is the same as the other one you're counting from 12/1/14 to 1/31/15! This is a problem when trying to make use of the titles as your delimiters. If you change the formula in S9 to reference the proper delimiting date of 12/31/14 you get the correct answer.
    Verada1.JPG

    I'll investigate the project totals but It may take some time for me to get to and understand it. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Verada,

    It appears as the problems with your totals not matching the by project values are due to your data.

    For example Jan-15 shows 1 project which does not show up in in the by project table. This is due to the Project Number for that 1 not being in the Project Number List, e.g. Adela! Note: I also adjusted the formulas in the S row (Jan-15) to reference S$7!
    Verada2.JPG

    Test File: rz-verada-1-2.xls

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #10
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks RG

    Ahh yes I can see that - that explains it.

    Also appears that the number of occurrences being calculated in the totals tab (row 9) is the number of occurrences in the actual month (say Dec-14 = 8) + the number of occurrence in previous month (Nov-14 = 11) = 19. Dec-14 should be 8 and Nov-14 should be 11.

    If you filter in the worksheet dates for 2014 November and December you get a count of 19.

    I assume that this has something to do with the date format as you indicated in your previous post. The problem I have is that the dates come from linked cells that use that format (MMM-YY)

    Any thoughts or other options?

    Thanks again for your help

  11. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Verada,

    Here's a new version that has a helper row 7 (note they are formulas!). You can choose to hide the row or change the font to White if you don't want to see the dates but I would recommending Hiding the row to prevent accidental changes or use the protection features of the worksheet.

    I've adjusted all the formulas to use the helper row and added a sum row (28) so you can check with row 9. Note some are different and I would guess that is because there are missing Project Numbers in the table. HTH

    rz-verada-1-3.xls
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. The Following User Says Thank You to RetiredGeek For This Useful Post:

    zeddy (2015-02-13)

  13. #12
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    RG - thanks for that, looks to be doing just as I need

    Thanks once again for your help now and in the past. Very much appreciated

    Regards
    Last edited by verada; 2015-02-12 at 23:15. Reason: Fixed range error - all working now

  14. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi RG

    ..thanks for sorting that out.
    It's great having another pair of eyes.

    zeddy

  15. #14
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by zeddy View Post
    It's great having another pair of eyes.
    Especially when the real data is hiding behind formatting...always a problem in Excel!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  16. #15
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Verada,

    You can also use a non-array formulas using Countifs:

    In cell G19 enter the formula
    =COUNTIFS(colB,">="&G$18,colB,"<" & H$18,colC,$F19)
    Cody down and across

    In Cell G9 enter the formula
    =COUNTIFS(colB,">="&G$18,colB,"<" & H$18)
    Copy across

    You also do not need the helper row 7 with the dates using this approach.

    HTH,
    Maud
    Attached Files Attached Files

  17. The Following 2 Users Say Thank You to Maudibe For This Useful Post:

    RetiredGeek (2015-02-14),verada (2015-02-16)

Page 1 of 2 12 LastLast

Posting Permissions

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