+ Reply to Thread
Results 1 to 7 of 7

Vlookup & multiple sumifs formula question

  1. #1
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Vlookup & multiple sumifs formula question

    Hello,
    I have tried to search this site and web for possible solutions without success. I am needing to use a combination of a vlookup with a sumifs formula that works between two workbooks as well as potentially two or more worksheets within one of the workbooks. The formula below works well if I'm using it to look at one worksheet.
    Please Login or Register  to view this content.
    But... if I want to use this formula to look at two worksheets in one workbook in combination with the vlookup I get a #Value. I'm hoping someone will be able to look at this and see what I'm doing wrong. I wish I could post a sample workbook(s) but it would be very difficult. Any suggestions to fix the following formula would be greatly appreciated!

    Problem Formula:
    Please Login or Register  to view this content.
    edited to add: I forgot to mention the sumifs portion is looking for data between two dates.
    Last edited by lilsnoop; 04-27-2014 at 07:43 PM. Reason: additonal info

  2. #2
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Vlookup & multiple sumifs formula question

    It's the first IF statement that is the problem. Your formula reads =IF(Condition,Value if true,Value if false),If(...

    This is not a valid formula syntax. The "value if false" of the first If statement is "", so I think if you remove the ,"") and add an extra ) at the end you should be good.
    Last edited by gak67; 04-27-2014 at 06:57 PM. Reason: Typo

  3. #3
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Vlookup & multiple sumifs formula question

    Hello gak67, Thanks for looking at this issue for me. The vlookup is working now as is the sumifs formula. Is there anyway that if the the first If statement is "False" then it would make the cell blank, which was why I tried to add the ""?

    Formula now:
    Please Login or Register  to view this content.

  4. #4
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Vlookup & multiple sumifs formula question

    You have the same IF condition in your formula twice, ie. IF(VLOOKUP(B4,'[Monthly_Report.xlsm]Time Summary'!$D$5:$FO$42,168,FALSE)="P". Can I clarify: If the condition returns "P" you want to add the values from the April and May sheets together?

  5. #5
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Vlookup & multiple sumifs formula question

    gak67-the IF(VLOOKUP(B4,'[Monthly_Report.xlsm]Time Summary'!$D$5:$FO$42,168,FALSE)="P" says to look at a particular workbook/sheet and if there is a "P" in the 168th column to conduct a sumifs formula in two worksheets in another workbook, which this formula will be located. I hope that clarifies.

    additional: The reason I have two formulas combined is because the formula sumifs hours of overtime worked in a pay period that started at a given date in April but ends in May. So I needed it to sum the data from April 20th to April 30th and then from May 1st to May 3rd.
    Last edited by lilsnoop; 04-27-2014 at 07:36 PM.

  6. #6
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Vlookup & multiple sumifs formula question

    Ok, so formula would be =IF(Condition="P",Sumifs(April)+Sumifs(May),""), or in full =IF(VLOOKUP(B4,'[Monthly_Report.xlsm]Time Summary'!$D$5:$FO$42,168,FALSE)="P",SUMIFS(April!D4:GA4,April!$D$2:$GA$2,">="&[BTPD_2014_Schedule.xlsb]Payroll!$MC$10,April!$D$2:$GA$2,"<="&[BTPD_2014_Schedule.xlsb]Payroll!$MD$10)-(SUM(April!GP4:GR4))+SUMIFS(May!D4:GG4,$D$2:$GG$2,">="&[BTPD_2014_Schedule.xlsb]Payroll!$ME$10,$D$2:$GG$2,"<="&[BTPD_2014_Schedule.xlsb]Payroll!$MF$10)-(SUM(GV4:GX4)),"")

  7. #7
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Vlookup & multiple sumifs formula question

    Excellent! Thanks so much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] sumifs, vlookup, multiple column rules
    By will.girling in forum Excel General
    Replies: 2
    Last Post: 03-11-2014, 10:54 AM
  2. [SOLVED] =SUMIFS Formula Question
    By Jsimpson19 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-10-2013, 11:01 PM
  3. SUMIFS Puzzle - Trying to avoid adding multiple SUMIFS to get valid result
    By haldavid in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-09-2013, 03:42 PM
  4. SUMIFS and VLOOKUP combination across sheets; dragging formula
    By alillian in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-09-2012, 10:21 AM
  5. Combined vlookup and sumifs in a single formula
    By mrexcelrc1 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 04-27-2012, 04:54 PM

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