+ Reply to Thread
Results 1 to 15 of 15

Sum, count, index, match problem! Help!!!

  1. #1
    Registered User
    Join Date
    07-26-2012
    Location
    Tetbury, England
    MS-Off Ver
    Microsoft 365 MSO
    Posts
    73

    Exclamation Sum, count, index, match problem! Help!!!

    Hi All,

    I have a problem which is probably simple to you guys, but an awful one for me!

    See The Attached Spreadsheet.

    What I want is, for each guy in Column AN to have a sum which works out how many claims they have booked in excluding cells with LETTERS between day 1 of Month, UP TO AND INCLUDING the DAY from the TODAY Function in Cell A2.

    Then in Column AO, I want similar, but just a count of the days excluding BLANKS and LETTERS that the member of staff was available between day 1 of Month, UP TO AND INCLUDING the DAY from the TODAY Function in Cell A2.

    Please can you help it's driving me Crazy!

    Thanks a lot!


    Chris

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Sum, count, index, match problem! Help!!!

    Hi waylettchris,

    For better understanding, can you put fewmanually calculated results in column AN and AO ? Thanks.


    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Sum, count, index, match problem! Help!!!

    Okay.. try looking at cells AN7 and AO7 where I used below formulas for your cases respectively :-

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    - > enter with key combination: ctrl shift enter
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    not sure if 'H' means holdiay in your data but not assuming as you did not mentioned

    where 'data' is a defined name.


    Surveyor Stats 2013 - DIRECT LABOUR - FOR EXCEL HELP FORUM.xlsxSurveyor Stats 2013 - DIRECT LABOUR - FOR EXCEL HELP FORUM.xlsx


    Regards,
    DILIPandey
    <click on below * if this helps>

  4. #4
    Registered User
    Join Date
    07-26-2012
    Location
    Tetbury, England
    MS-Off Ver
    Microsoft 365 MSO
    Posts
    73

    Exclamation Re: Sum, count, index, match problem! Help!!!

    Hi dilipandey!

    Spread sheet attached with top 5 guys manually calculated as examples in Columns AN & AO!

    Hope You Can Help!

    Chris

  5. #5
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Sum, count, index, match problem! Help!!!

    you are late my friend... in the mean time I already provided my solution in post#3



    Regards,
    DILIPandey
    <click on below * if this helps>

  6. #6
    Registered User
    Join Date
    07-26-2012
    Location
    Tetbury, England
    MS-Off Ver
    Microsoft 365 MSO
    Posts
    73

    Re: Sum, count, index, match problem! Help!!!

    Hi Dilipandey!

    Thanks so much for your help on this, quick question though. The offset formula you used for the "data" named range as seen below:

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


    My question is, how would I adapt this OFFSET formula to make the named range appear as every day between TOMORROW and the last day named in range E6:AI6?

    To quantify, if the date today was 28/05/2013, I would want the named range to be 29/05/2013 - 31/05/2013?

    Hope this makes sense?!

    Hope you can Help! You've saved my life here!

    Regards,


    Chris

  7. #7
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Sum, count, index, match problem! Help!!!

    Okay.... share an sample workbook with your above data arrangement and I would be happy to do this for you.


    Regards,
    DILIPandey
    <click on below * if this helps>

  8. #8
    Registered User
    Join Date
    07-26-2012
    Location
    Tetbury, England
    MS-Off Ver
    Microsoft 365 MSO
    Posts
    73

    Exclamation Re: Sum, count, index, match problem! Help!!!

    Thanks so Much!

    Spreadsheet attached. Just so you know, I have renamed your named range from data, to data_MAY.

    If you could let me know what you name this range when you make it in your reply I would be appreciative so I can find it easily! Thanks so much!

  9. #9
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Sum, count, index, match problem! Help!!!

    Okay.. I have enhanced the define name formula as below:-

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


    Surveyor Stats 2013 - DIRECT LABOUR - v3.0 for EXCEL HELP FORUM.xlsx

    define range name still the same


    Regards,
    DILIPandey
    <click on below * if this helps>

  10. #10
    Registered User
    Join Date
    07-26-2012
    Location
    Tetbury, England
    MS-Off Ver
    Microsoft 365 MSO
    Posts
    73

    Re: Sum, count, index, match problem! Help!!!

    Hi Dilipandey,

    Not sure if I have been misunderstood or not, so feel free to correct me. What I want is the named range you wrote originally called "data" which did everything before today's date to stay the same. Then I want a SEPARATE named range which was everything from TOMORROW until the end of the month. There will be 2 in total, one for past, and one for future. TODAY should be included in the PAST named range.

    I don't know if that is what you have done as I only have one named range and I can't see that it can do both tasks?

    Thanks for your help in advance!

    Chris

  11. #11
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Sum, count, index, match problem! Help!!!

    Ohkk.. so the revised name is now for your revised requirement and for earlier one get that from post #8 above

    So post #8 and #9 have both the names, just copy anyone in other one and rename the defined name to avoid clash

    Regards,
    DILIPandey
    <click on below * if this helps>

  12. #12
    Registered User
    Join Date
    07-26-2012
    Location
    Tetbury, England
    MS-Off Ver
    Microsoft 365 MSO
    Posts
    73

    Re: Sum, count, index, match problem! Help!!!

    Hi Dilipandey,

    Sorry to be a pain, and not to appear dumb, but to avoid mistakes on my part, could you do it for me? You don't have to do it in the spreadsheet, just reply to the post with the formulas and I can input it myself.

    As I said before, I need one which includes from start of month i.e. day 1, up until and including TODAY'S date. Then a second separate one which does from TOMORROW until the end of the month i.e. 31. Obviously as not all months have the same number of days, the one which does from TOMORROW onwards, needs to be able to stop at 28, 29, 30 or 31 depending on how many days there are in the month in question!

    Thanks a lot Dilipandey! Sorry to ask you to spoon feed me like this!

    Regards,

    Chris

  13. #13
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Sum, count, index, match problem! Help!!!

    Hi Chris,

    Here are the two formulas for defined names:-

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


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


    apply them and name them as you want


    Regards,
    DILIPandey
    <click on below * if this helps>

  14. #14
    Registered User
    Join Date
    07-26-2012
    Location
    Tetbury, England
    MS-Off Ver
    Microsoft 365 MSO
    Posts
    73

    Exclamation Re: Sum, count, index, match problem! Help!!!

    Hi Dilipandey!

    Need more help, (sorry!) for some reason the offset formula for the named range until the end of the month, keeps referencing row AH, rather than the row where I have entered the formula.

    To explain better, if I enter a formula in row 7 somewhere, which wants to calculate the sum of the visits booked between TOMORROW and the end of the month, I want it to add up the visits booked on ROW 7? Not row 32? Then I want it so if I drag the formula down to ROW8, 9, 10 etc. it will continue to reference that row?

    This is how the formula for the month to date worked which you made named "data".

    As an example I have attached a spreadsheet. Look in Cell AX7. I have asked for the sum of "Until_end_MAY" which gives me 104, which is cells AH32+AI32 added together. This tells me the date part of it is working, but the row is wrong, I want it to be the sum of the columns between tomorow and end of month, but in the same ROW as where the named range has been entered? In actual fact, what I want is for the answer to be 5, which is AH7+AI7 added together? If that makes sense?

    Thanks!

    Chris

  15. #15
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Sum, count, index, match problem! Help!!!

    Hi Chris,
    As an example I have attached a spreadsheet. Look in Cell AX7. I have asked for the sum of "Until_end_MAY" which gives me 104, which is cells AH32+AI32 added together.
    Which tab to refer here... workbook opened in tab May'13 and I am not able to relate what you said?



    Regards,
    DILIPandey
    <click on below * if this helps>

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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