# Vlookup & multiple sumifs formula question

1. ## 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.  Register To Reply

2. ## 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.  Register To Reply

3. ## 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.``  Register To Reply

4. ## 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?  Register To Reply

5. ## 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.  Register To Reply

6. ## 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)),"")  Register To Reply

7. ## Re: Vlookup & multiple sumifs formula question

Excellent! Thanks so much!  Register To Reply