Find totals by week
I have a spreadsheet with various dates(a) (sequential) and pay(G). I would like to fill in the Week total column(h) with the total made for the week in the last week of the row. In the attached picture, I manually entered the values to show what I want to do.
Added table as requested
| Date | Start | End | Subject | TimeSpent | Pay | WeekTotal |
|---|---|---|---|---|---|---|
| 01/10/2024 | 533.42 | |||||
| 01/12/2024 | 595.44 | 1128.86 | ||||
| 01/15/2024 | 496.20 | |||||
| 01/16/2024 | 248.10 | |||||
| 01/16/2024 | 248.10 | 992.40 | ||||
| 01/23/2024 | 471.39 | 471.39 | ||||
| 01/29/2024 | 595.44 | |||||
| 01/30/2024 | 285.32 | |||||
| 01/31/2024 | 372.15 | |||||
| 02/01/2024 | 372.15 | 1625.06 | ||||
| 02/06/2024 | 372.15 | 372.15 | ||||
| 02/12/2024 | 124.05 | |||||
| 02/13/2024 | 372.15 | |||||
| 02/14/2024 | 434.18 | 930.38 |
Can someone help with the formula?
Scott
Solution
For the older versions without Office 365, This will work in All versions:
=IF(WEEKNUM(A2)=WEEKNUM(A3),"",SUMPRODUCT($F$2:$F$15*(WEEKNUM(--$A$2:$A$15)=WEEKNUM(A2))))
OR
This is shorter, but it requires that the dates are sorted:
=IF(WEEKNUM(A2)=WEEKNUM(A3),"",SUM($F$2:F2)-SUM($G$1:G1))
- How to import values from excel with pandas into tkinter faster?
- Speed up concatenation of excel files with Pandas
- Trying to send screenshot of excel in an email
- Sum of text Does not contain in Range
- Stopping Excel Macro executution when pressing Esc won't work
- Consolidated Date window
- Excel formula for calculating difference between two date columns
- Get number of decimals in python float, including trailing zeros
- Excel VBA search for data and stuck at a certain point due to group or split source file?
- Convert Time Input in Decimal Format - Excel Formula or VBA
- Lookup unique "Values" if they follow a certain "Pattern" from a lookup table then return a "Name"
- Share office scripts connected to Excel with external organisations
- Tiling an Excel array in 2 dimensions, ideally not using VBA
- How to get the cartesian product of two sets via Excel Formulas
- Repeat Value for Every Instance of Another Value in Excel without using Power Query
- VSTACK doesn't pile up dynamic ranges
- How To Close a UserForm Properly in Excel VBA?
- In Excel, can I use a hyperlink to run vba macro?
- EXCEL: Count all unique values in Table column up to a certain point, and combine them into a single string?
- Excel VBA connection to SQL Server with Microsoft Entra MFA authentication
- Excel shows incorrect polynomial trendline function
- Split too large VBA into multiple Subs
- Trying to calculate user whos spent the most and the least - Excel
- How to do a union of two columns and then sort them into a third column in Excel?
- Is there a way to create a simple custom formula in Excel without VBA?
- Copying a String Array into a Range of cells (in MS Excel using VBA)
- vlookup with formula calculated from lookup cell location
- How to extract R InstallPath from Registry using Excel VBA
- How to inner-join in Excel (eg. using VLOOKUP)
- Scientific notation when importing from Excel in .Net