Results 1 to 6 of 6

Thread: Formula Help

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

    Formula Help

    Hi Loungers

    The attachment indicates the value I'm trying to return (see Sheet 1), the data in the Worksheet is exported from a database.

    I've tried different way to match two criteria to return the value (of 28) but keep getting #N/A errors

    Any help would greatly appreciated.

    Regards
    Attached Files Attached Files

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

    Try this formula:

    Code:
    =INDEX(Worksheet!$A$8:$L$5000,MATCH($A6,Worksheet!$E8:$E$5000,0),11)
    Last edited by Maudibe; 2014-09-03 at 08:45.

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Hi Maudibe - thanks for the reply, however the returned value also need to match the month (in column B) as well as the project name (in column E). any suggestions?

    Also do you have any thoughts on why the formulas that I used in the attached file didn't work?

    Thanks again - regards

  4. #4
    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 one that works on your test data:
    M6: =SUMIFS(Worksheet!$I$8:$I$29,Worksheet!$B$8:$B$29, ">="&DATE(YEAR(M$5),MONTH(M$5),1),Worksheet!$B$8:$ B$29,"<="&DATE(YEAR(M$5),MONTH(M$5),DAY(EOMONTH(M$ 5,0))),Worksheet!$E$8:$E$29,Sheet1!$A6)

    This formula is dragable, e.g. you can drag it back to B6 and down as far as you wish. Please note I only included down to row 29 in the ranges so if you have more data you'll have to adjust all the 29s in the formula. Or the ranges could be replaced with Dynamic Range Names which would solve the problem of changing data volumes.

    Test File: woodyfix.xlsx

    Note: You didn't specify which version of Excel you have. This solution will only work in Excel 2007 and up. I know your test file was an .xls (pre 2007) file so you may need another answer. It can probably be converted to a SumProduct formula but I'm too tired to figure that out tonight as it will be quite complicated, at least for me!

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks HTH - that works a treat.

    Much appreciated

  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,

    Glad it worked for you, I didn't really want to figure out the SumProduct equivalent.

    FYI: HTH = Hope this helps!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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