How to Calculate Training Units by Fiscal Year with SUMPRODUCT in WPS Spreadsheets
Question details
The user needs a SUMPRODUCT formula that totals education units by fiscal year only when a completion date exists, and the formula should be reusable across multiple training topics.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user manages a training matrix with education-unit values and fiscal-year completion date columns, and needs totals per selected fiscal year.
- Observed behavior
- The formula should return the total education units for rows that contain a completion date under the selected fiscal-year header, ignoring blank or incomplete rows.
Open the training workbook in WPS Spreadsheets and confirm the unit values are in one column, the fiscal-year headers are in the header row, and incomplete completion date cells are blank.
Use SUMPRODUCT to Total Units for a Selected Fiscal Year
Use this solution when you need a reusable total that only counts rows with a completion date under the matching fiscal-year column.
The formula multiplies a TRUE/FALSE date check, the education-unit values, and a TRUE/FALSE header check. TRUE acts as 1 and FALSE acts as 0, so only completed rows under the selected year contribute to the total.
In WPS Spreadsheets, click the cell where the fiscal-year total should appear, such as C16.
Type =SUMPRODUCT(($D$2:$G$12<>"")*($B$2:$B$12)*($D$1:$G$1=C15)) and press Enter. Replace the ranges with your actual unit column, date range, header row, and year cell.
Drag the fill handle across the other fiscal-year columns or reference the appropriate year cell so the header comparison updates for each column.
Calculate Fiscal-Year Education Unit Totals in WPS Spreadsheets
WPS Spreadsheets gives you Excel-compatible SUMPRODUCT formulas, so you can directly build a reusable fiscal-year training total without changing your .xlsx workbook or leaving the familiar spreadsheet workflow.
- 1. Download and install WPS Office: Get WPS Office from the official WPS site and open WPS Spreadsheets.
- 2. Prepare the training workbook: Open your existing Excel workbook or create a new sheet with unit values in column B, fiscal-year headers in D1:G1, and completion dates in D2:G12.
- 3. Enter the SUMPRODUCT formula: Click the result cell and enter =SUMPRODUCT(($D$2:$G$12<>"")*($B$2:$B$12)*($D$1:$G$1=C15)).
- 4. Copy and reuse the formula: Copy it across other year columns or adjust ranges for additional training topics.
- 5. Check the results: Verify totals update correctly when you add or remove completion dates.
Frequently Asked Questions
Why does the SUMPRODUCT formula return 0 when some completion dates exist?
Confirm that the year cell C15 exactly matches a header in D1:G1, and check that the completion date cells are not empty strings or spaces. In WPS Spreadsheets, the condition $D$2:$G$12<>"" counts any non-blank cell.
Can I reuse this formula for different training topics?
Yes. Keep the unit values in the corresponding rows and the fiscal-year headers in the same position, then copy the formula and adjust the ranges to match the new training topic block.
Does WPS Spreadsheets support Excel SUMPRODUCT array formulas?
Yes. WPS Spreadsheets supports Excel-compatible SUMPRODUCT formulas, including comparisons across a date range and a header row, and opens .xlsx files without converting them.
How do I copy the formula without changing the fixed ranges?
Use absolute references for the unit column and date range, such as $B$2:$B$12 and $D$2:$G$12. The result cell and year cell can be relative or adjusted so each fiscal-year column picks the correct header.
