Sum WPS Spreadsheet Values for a Financial Year Using Dates
Question details
Total the amounts in column E only when the corresponding dates in column H are within the financial year from July 1, 2023, to June 30, 2024.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user needs a financial-year total based on an amount column and a date column.
- Observed behavior
- The required result is a sum that includes every valid date and time from July 1, 2023, up to but not including July 1, 2024.
Verify that column H contains real spreadsheet date values, not text that only looks like a date. The exclusive July 1, 2024, end date ensures entries on June 30 with a time value are included.
Use SUMIFS with financial-year start and exclusive end dates
Use this method to calculate one total for amounts in column E whose matching dates in column H fall within the 2023–2024 financial year.
SUMIFS applies both date conditions to the same date range. Using DATE prevents regional date-format issues, while using a less-than condition for July 1, 2024, includes all times on June 30, 2024.
Click the empty cell where you want the financial-year total to appear.
Type or paste =SUMIFS(E:E,H:H,">="&DATE(2023,7,1),H:H,"<"&DATE(2024,7,1)) and press Enter.
Select a value in column H and look at the formula bar. If it is text, convert it to a date using Data > Text to Columns or a date-conversion formula before calculating the total.
Use cell references for a reusable financial-year total
Use this option when the financial-year start date may change and you want to update the formula without editing it.
Calculate Financial-Year Totals with SUMIFS in WPS Spreadsheets
WPS Spreadsheets lets you use SUMIFS with DATE-based criteria to total transactions for an exact financial-year range. Enter the formula once, or reference start and end date cells to make the report easy to update each year.
- 1. Open the worksheet in WPS Spreadsheets: Open the workbook containing amounts in column E and dates in column H.
- 2. Choose the total cell: Click an empty cell below or beside your report where the financial-year result should display.
- 3. Add the date-based formula: Enter =SUMIFS(E:E,H:H,">="&DATE(2023,7,1),H:H,"<"&DATE(2024,7,1)).
- 4. Confirm the calculation: Press Enter and verify that the result includes records dated through June 30, 2024.
Frequently Asked Questions
Why should the end condition use <DATE(2024,7,1) instead of <=DATE(2024,6,30)?
A less-than July 1 condition includes every time on June 30. A value such as June 30, 2024 3:00 PM is greater than the midnight value of June 30 and may be excluded by a less-than-or-equal condition.
Can I use a fixed date such as "7/1/2023" in the SUMIFS formula?
You can, but DATE(2023,7,1) is more reliable because it is interpreted consistently regardless of the spreadsheet's regional date settings.
Why does SUMIFS return 0 when there are dates in column H?
The values in column H may be text rather than real dates. Click a cell and inspect the formula bar, then convert text dates using Data > Text to Columns or an appropriate conversion formula.
How do I change the formula for the next financial year?
Change the first DATE value to the new financial-year start and the second DATE value to the following July 1. For example, use DATE(2024,7,1) and DATE(2025,7,1) for the 2024–2025 year.
