Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

#Value! Error in sumifs

0

Hi,  I am trying to edit an existing formula to enable it to add up "hours" (Column I) for a specific "job #" (Column D) for specific "payroll categories" (Column C) between a specified date parameter.

The existing formula for adding up the Hours for the Job & Payroll Category is:

=SUMIFS('Job Hrs Raw Data'!$I$2:$I$200000,'Job Hrs Raw Data'!$C$2:$C$200000,"1.5x Overtime",'Job Hrs Raw Data'!$D$2:$D$200000,$D$35)

I now want to edit it so that it works between 2 dates ie. 01.05.2021 to 31.05.21 & gives the summary of hours. This is my Raw Data below.

Shirley 1.5x Overtime 6151 13/05/2021 0.25 Walker Fab/Weld 6195 13/05/2021 8.00 Wormer Fab/Weld 6164 13/05/2021 5.00 Wormer Leave Without Pay   13/05/2021 3.00 Clarke Fab/Weld 6164 14/05/2021 6.00 Elias Fab/Weld 6164 14/05/2021 6.00 Ham Fab/Weld 6164 14/05/2021 6.00 Levi Site Work/Installation 6151 14/05/2021

6.00

This is my summary of hours.

PRODUCTIVE     1.5 x Overtime Leading Hand   #VALUE! 1.5x Overtime    $                  71.50 2x Overtime    $                       -   Allowance - Meal    $                       -   Base Hourly Wages    $                       -   Del/Load/Unload    $                       -   Fab/Weld    $             2,066.25 Leading Hand Higher Duties    $                       -   QA/Workshop Duties    $                       -   Site Work/Installation    $                       -   Sunday Travel Time    $                       -   Tech/Training    $                    5.00 Travel Time    $                       -   Unpaid Job Hours    $                       -   Total Hours   #VALUE!

I have set up the date paremeters as

Do not touch dates Start End Use 1/06/2021 30/06/2021             Do not touch dates, they have formula's     Period Start End January. 2021 1/01/2021 31/01/2021 February. 2021 1/02/2021 28/02/2021 March. 2021 1/03/2021 31/03/2021 April. 2021 1/04/2021 30/04/2021 May. 2021 1/05/2021 31/05/2021 June. 2021 1/06/2021 30/06/2021

Just needing some assistance to correct the #value! error, if anyone can assist me.

Thanking you in advance.

Bronski

Answer
Discuss

Discussion

Hi Bronski and welcome to the Forum

Please edit your original question to attach your Excel file (using the Add Files button). That will make it easier for contributors to check your formula against data (without needing to recreate your file) 
John_Ru (rep: 4312) Jun 29, '21 at 1:35 am
Add to Discussion

Answers

0

Bronski

Given you didn't upload an Excel file (please do so next time), I've had to guess where your date fields are. My modified formula below assumes that the worked hours dates are in column E of the sheet Job Hrs Raw Data and your Summary sheet has data here:

$D$33 Start date

$D$34 End Date

$D$35 Payroll code

If not, you'll need to change the bits in bold below:

=SUMIFS('Job Hrs Raw Data'!$I$2:$I$200000, 'Job Hrs Raw Data'!$C$2:$C$200000, "1.5x Overtime", 'Job Hrs Raw Data'!$D$2:$D$200000, $D$35, 'Job Hrs Raw Data'!$E$200000, 'Job Hrs Raw Data'!$E$2: $E$200000,">="&$D$33,'Job Hrs Raw Data'!$E$2:$E$200000,"<="&$D$34)
The formula looks very unwieldy (do you really have 200,000 rows of data?) so I suggest you look at using Named Ranges to simplify it.- e.g. see Don's tutorial Easy Way to Manage Names in Excel

Hope this works for you.

Discuss

Discussion

No joy with that suggestion?
John_Ru (rep: 4312) Jun 30, '21 at 4:28 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login