Summing multiple lines based off of date in single cell?

JustS0meGuy

New Member
Joined
Jul 16, 2019
Messages
2
Hello,
I have to do these tedious time sheets everyday. I'm trying to spend some upfront time to hopefully same me time in the long run. Below is a link to an example file.

I found code to get unique project numbers from the list, but I can't figure out how to sort them (yellow column)

With the format used, the date is only entered once for the entire day. As a result I can't figure out a non-VBA way to sum the hours under the red highlighted area. I can't figure out a good VBA approach with my (very) limited skills. Would any of you kind VBA kung-fu masters be able to assist?


https://www.dropbox.com/sh/s9khekp5rtear4p/AAAozJv4tiJE2lpKuNAJp8d7a?dl=0
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
34,004
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
This will fill in the dates in col A, so you can then use SUMIFS in cols F:L
Code:
Sub Justsomeguy()
   With Range("B5", Range("B" & Rows.Count).End(xlUp))
      .SpecialCells(xlConstants).Offset(, -1).SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
   End With
End Sub
 

JustS0meGuy

New Member
Joined
Jul 16, 2019
Messages
2
Thanks for getting back to me! This works great for the formatting issue. This sheet is used by others so I knew entering the date for every line wouldn't be a problem for me but getting others to be consistent would be the issue.

The next task is the SUMIFS. I’m going to try to put the basic =SUMIF in each cell, but I’ve had issues in the past with Excel getting angry about too many formulas testing too many conditions. If I have an issue my next idea was to put a VBA SUMIF code that would loop through the table and populate each cell. My thought is using the VBA would make it so Excel was only doing hard work when requested as opposed to having the basic =SUMIF functions all firing at the same time whenever anything is updated in the workbook. Any thoughts on which would be better?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
34,004
Office Version
365
Platform
Windows
I would stick with SUMIFS like
=SUMIFS($B$5:$B$17,$A$5:$A$17,F$4,$C$5:$C$17,$E5)
This will only recalculate if the data in A5:C17 changes, or if the criteria cells change
 

Forum statistics

Threads
1,086,189
Messages
5,388,310
Members
402,114
Latest member
seanbrown01

Some videos you may like

This Week's Hot Topics

Top