Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Apr 2014
    Posts
    2
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Question How to pull a cell value on separate worksheet based on multiple criteria

    I have a workbook used to track campaign costs for multiple entities - i.e. total value of leads purchased per entity per lead provider. I am stuck trying to write a formula that will pull the correct lead rate from a different worksheet within the workbook. As lead rates change, a start date and end date are entered as well as the new lead rate. I tried this formula, =SUM(SUMIFS(CampaignAmount,Campaign,B$2,CampaignCo mpany,$A2,StartDate,"<="&$C2)), but it is totaling all of the Campaign Amounts listed. I need it to return the amount based on most recent start date and after the most recent end date. Sample worksheets are attached.

    Can anyone help me see what I'm doing wrong?

    Many Thanks!
    Attached Files Attached Files

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    New Lounger
    Join Date
    Dec 2013
    Posts
    14
    Thanks
    1
    Thanked 3 Times in 3 Posts
    I believe you can accomplish what you want using the VLOOKUP function which accesses the rate data in Worksheet#2. Attached is a modified version of your spreadsheet.

    To use VLOOKUP you need to create a lookup key in Worksheet#1 which concatenates the Entity, Campaign, and Date fields. See col. A of the attached worksheet.

    The data in Worksheet#2 must be sorted by CampaignCompany, Campaign, StartDate. Then a key field needs to be added which concatenates the CampaignCompany, Campaign, and StartDate fields. See col. M.

    The range of data cells in col. M and N has been named RatesTbl. The formula to lookup the rates in col. F is (for row 3), =VLOOKUP(A3,RatesTbl,2). It uses the Lookup Key in col. A, attempting to find a match in col. M, the first col. of the RatesTbl and return the corresponding rate from col. N. If it doesn't find an exact match, then it returns the next largest value that is less than the lookup value, which is what you want.

    I believe this will always work as long as Worksheet#2 contains an entry for every combination of Entity and Campaign in Worksheet#1, along with a StartDate that is on or before the earliest Date in Worksheet#1 for each such combination.LeadCostsTracking-rev1.xlsx

  4. The Following User Says Thank You to THill For This Useful Post:

    ljalv66 (2014-04-02)

  5. #3
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,194
    Thanks
    44
    Thanked 226 Times in 210 Posts
    Ijalv66,

    Is this what you are looking for?

    =SUMIFS(CampaignAmount,Campaign,$B3,CampaignCompan y,$A3,StartDate,"<="&$C3,StartDate,">"&MAX(EndDate ))

    Sumifs.png
    Attached Files Attached Files

  6. #4
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,194
    Thanks
    44
    Thanked 226 Times in 210 Posts
    Don't know why the editor keeps placing spaces in the formula above (CampaignCompan y and (EndDate ). Please remove them if pasting the formula. Note: Referring to your sample worksheet, in your original formula, please change B$2 to $B2 and start formula on row 3 instead of 2 as noted in above formula. You do not need the outside SUM formula if I am understanding correctly what you want to do.

    Maud
    Last edited by Maudibe; 2014-04-01 at 22:06.

  7. The Following User Says Thank You to Maudibe For This Useful Post:

    ljalv66 (2014-04-02)

  8. #5
    New Lounger
    Join Date
    Apr 2014
    Posts
    2
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thank you, Thank you! This was perfect! I appreciate you taking the time to look it over and offer a solution, just in time for my meeting

Posting Permissions

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