'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Use Excel Formulas to Count Complete and Incomplete Training Records
Question details
The user needs to calculate the number of complete and incomplete training records by comparing retraining dates against expiration dates using Excel formulas.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking employee or student training completions and analyzing whether retraining occurred before or after expiration dates.
- Observed behavior
- Needs a dependable formula to compare two date columns row by row and count the occurrences while ignoring blank cells.
Ensure that your expiration dates and retraining dates are formatted as valid dates in Excel, and identify the exact column letters and row numbers containing your data.
Use SUMPRODUCT for Reliable Row-by-Row Date Comparison
SUMPRODUCT is the most reliable function for comparing two ranges row-by-row across all Excel versions, especially when ignoring blank cells.
While COUNTIFS is popular for counting based on criteria, it cannot natively compare two ranges against each other row by row in some Excel versions. SUMPRODUCT overcomes this limitation by evaluating arrays of conditions and converting TRUE/FALSE results into analyzable numbers.
Click on an empty cell where you want to display the total number of complete training records.
Type the formula =SUMPRODUCT(--($H$2:$H$1000<>""),--($G$2:$G$1000<>""),--($H$2:$H$1000<=$G$2:$G$1000)) into the formula bar. In this example, column H contains the retraining dates and column G contains the expiration dates.
In another empty cell, calculate the incomplete records (where retraining happened after expiration) by changing the comparison operator: =SUMPRODUCT(--($H$2:$H$1000<>""),--($G$2:$G$1000<>""),--($H$2:$H$1000>$G$2:$G$1000)).
Press Enter to apply the formulas. Be sure to adjust the range $H$2:$H$1000 and $G$2:$G$1000 to perfectly match the rows and columns of your actual dataset.
Count and Analyze Training Records in WPS Spreadsheet
WPS Spreadsheet offers full compatibility with advanced formulas like SUMPRODUCT and COUNTIFS. You can easily track training statuses, manage large datasets, and accurately calculate complete or incomplete records without any hassle.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your training record dataset (.xlsx or .xls).
- 2. Locate your date columns: Identify the columns containing your training expiration dates and retraining completion dates.
- 3. Input the formula: Select a blank cell and input the SUMPRODUCT formula for your row-by-row date comparison.
- 4. Get instant calculations: Press Enter to instantly count the completed or incomplete records, seamlessly ignoring blank cells.
Frequently Asked Questions
Why does my COUNTIFS formula return an error when comparing two ranges?
In many versions of Excel, COUNTIFS does not support direct row-by-row comparisons between two ranges (e.g., comparing Range1 <= Range2). To bypass this limitation, you must use SUMPRODUCT or an array formula instead.
How do I ignore blank cells in my date comparison formulas?
Add a condition to check that the cell is not empty using <>"". In SUMPRODUCT, this looks like --($H$2:$H$1000<>""), which ensures blank rows are not accidentally evaluated as zero and counted as complete.
Can I use an IF statement instead of SUMPRODUCT for this calculation?
Yes, you can use an IF statement combined with SUM (e.g., =SUM(IF(H2:H1000<=G2:G1000, 1, 0))), but you must press Ctrl+Shift+Enter to execute it as an array formula in older versions of Excel. SUMPRODUCT is generally preferred because it natively handles arrays without requiring special keystrokes.
