Hello everyone. I am creating a spread sheet to calculate hours worked. Currently, I am struggling to get a formula to calculate how many hours worked per month. They want to days to be running. Meaning instead of having January and February separate, have them flow together as seen in the photo below. The problem with this is it mixing the months. In the first table, I have Dec, Jan, and Feb all listed. Is there a way to write a formula to state that if one cell has a particular text (or in this case month), that it will calculate the total hours worked?
So for example, if Cells E:3-E:9 have Jan in the box, then count the hours marked in Cells H:3-H:9. However, I would want to do this for the entire table. Is this doable?
Thank you!
@Ramer21 Unless that is some mandated format, I would recommend you change how your sheet is laid out. Here is an example of how I created a timesheet:
The left most column is actually auto-generated base on the year I enter and even the color bands indicating weekends and holidays are conditional formatting. The 2nd column is the calculated hrs for the day. The 3rd column is a location code for which site I went to. Columns 4 on are where I enter my start time for each job followed by the job code followed by the stop time for that job and start time for the next job code (I can enter non-jobs like 'lunch' or 'break' as needed for space holders).
I then have multiple other sheets that are set up in different 'reporting' formats for different jobs (i.e. the way that job want to see it) and do daily and monthly totals on those sheets (I have a month drop-down at the top so I just select which month I want the report for and it updates accordingly).
That all said, your answer is you can use SUMIFS() so for cells E3:E9 you have a formula like:
=SUMIFS(E3:E9, A3:A9,">="&DATE(2022,1,1), A3:A9,"<"&DATE(2022,2,1) )
or you can also you SUMPRODUCT and the advantage is you can do an array calculation and compare the MONTH of the dates to 1 (i.e. Jan) instead of making sure it is >- Jan 1 and < Feb 1:
=SUMPRODUCT( (E3:E9)*(MONTH(A3:A9)=1) )