Use a Sum If weekday function to return hours worked by day of week

**jbeets** · 09-19-2015, 12:05 PM

Hello,

I am trying to return the total hours worked for all employees by day of week however I am running into some complications- I was trying to us =Sum(If(And(Weekday formula but I keep getting an error so clearly I am doing something wrong. I think it is because the way the spreadsheet is laid out the Criteria range and the sum range are the same but I don't know how to get around that.

I have attached my spreadsheet- I want to return the values onto Sheet 1- Looking at Employee Calendars tab for each day of week and then summing the total hours worked for that day.

Please let me know if you more details or anything else and THANK YOU so much for taking the time to help me!

Jodi

**Tony Valko** · 09-19-2015, 12:13 PM

I didn't download your file (too big!)

If this is your data:

Data Range

	A	B
1	Date	Hours
2	9/3/2015	7
3	9/27/2015	1
4	9/20/2015	2
5	9/28/2015	5
6	9/14/2015	8
7	9/10/2015	9
8	9/13/2015	4
9	9/4/2015	7
10	9/13/2015	11
11	9/13/2015	9
12	9/9/2015	7
13	9/29/2015	7
14	9/18/2015	12
15	9/23/2015	1

To get the sum total for Sunday:

=SUMPRODUCT(--(WEEKDAY(A2:A15,2)=7),B2:B15)

7 = weekday number for Sunday

**Tony Valko** · 09-19-2015, 12:23 PM

P.S.

If the hours are in time format then format the formula result as [h]:mm.

The [ ] brackets keep the hours from rolling over into days.

**jbeets** · 09-19-2015, 12:24 PM

Hi Tony,

Thank you for getting back to me so quickly! Here is an example of what my spreadsheet looks like:

8/1/2015 8/1/2015 8/1/2015 8/1/2015 8/2/2015 8/2/2015 8/2/2015
Saturday Saturday Saturday Saturday Sunday Sunday Sunday
HOURS JOB TYPE Pay Rate PIECES HOURS WORKED PW Categories JOB TYPE
10.00 Enclave 0.00 0.00 Enclave
0.00 V-Train 0.00 0.00 V-Train
0.00 Laundry 0.00 0.00 Laundry
0.00 Janitor 0.00 0.00 Janitor
0.00 Recycle 0.00 0.00 Recycle
0.00 Resi Rec 0.00 0.00 Resi Rec
0.00 Shred 0.00 0.00 Shred
0.00 Thrift 0.00 0.00 Thrift
0.00 Clean 0.00 0.00 Clean
0.00 0.00 0.00
0.00 0.00 0.00
0.00 0.00 0.00
0.00 0.00 0.00
0.00 0.00 0.00
0.00 0.00 0.00
10.00 0.00 0.00

Its kind of hard to look at this this format- but the details for each employee go from left to right.
Row 1 Date
Row 2 DOW
Row 3 Hours Worked listed down this column.

I hope that makes some sense.

Thanks again!!
Jodi

**Tony Valko** · 09-19-2015, 12:27 PM

If you were to put that data in a sample file it would be about 9 KB in size and more people would be inclined/willing to look at it!

**jbeets** · 09-19-2015, 12:30 PM

Makes sense- Ok I have attached a sample file.

Thank you again!

Jodi

**jbeets** · 09-19-2015, 12:39 PM

Sorry- this one is even smaller- very sorry to be so annoying! :eek:

**Tony Valko** · 09-19-2015, 02:55 PM

OK, I've downloaded the file.

What exactly are you wanting to do? Tell us what result you expect.

**jbeets** · 09-19-2015, 06:37 PM

Thank you- So I want to return the sum of the total hours worked by day of week. So on the Sheet 1 tab I want the formula to go to the employee calendars tab and look for all of the Mondays, Tuesdays, etc and return the SUM of the HOURS Worked for all employees for each day.

In the sample file Saturday and Monday would both equal 10.

Thank you very much for your help!
Jodi

**Tony Valko** · 09-19-2015, 06:49 PM

Try this...

On Sheet1 cell B3 change the entry to Hours Worked. You just have Hours in there but in some of the other cells (F3, J3) you have Hours Worked. So, make all those cells consistent.

Then, on some other sheet:

Data Range

	A	B
1	Day	Total
2	Monday	10
3	Tuesday	0
4	Wednesday	0
5	Thursday	0
6	Friday	0
7	Saturday	10
8	Sunday	0

This formula entered in B2 and copied down:

=SUMIFS(Sheet1!B$19:M$19,Sheet1!B$2:M$2,A2,Sheet1!B$3:M$3,"Hours Worked")

**jbeets** · 09-19-2015, 07:39 PM

Tony- This works perfectly! Thank you for all of your help and patience with me!

**Tony Valko** · 09-19-2015, 07:45 PM

You're welcome. Thanks for the feedback!

Use a Sum If weekday function to return hours worked by day of week

LinkBack

Thread Tools

Rate This Thread

Display

Use a Sum If weekday function to return hours worked by day of week

Re: Use a Sum If weekday function to return hours worked by day of week

Re: Use a Sum If weekday function to return hours worked by day of week

Re: Use a Sum If weekday function to return hours worked by day of week

Re: Use a Sum If weekday function to return hours worked by day of week

Re: Use a Sum If weekday function to return hours worked by day of week

Re: Use a Sum If weekday function to return hours worked by day of week

Re: Use a Sum If weekday function to return hours worked by day of week

Re: Use a Sum If weekday function to return hours worked by day of week

Re: Use a Sum If weekday function to return hours worked by day of week

Re: Use a Sum If weekday function to return hours worked by day of week

Re: Use a Sum If weekday function to return hours worked by day of week

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Figure out the hours worked for the week

Bookmarks

Bookmarks

Posting Permissions