WPS Spreadsheet: Count and Sum Records with Multiple Criteria
Question details
The user needs to count records and total weekly hours using several criteria, including a second calculation that excludes the Reablement service.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A summary sheet must report hospital and destination records by service and week-ending date, with separate results for Reablement and all other services.
- Observed behavior
- The required multi-criteria counts and weekly-hour totals have not yet been calculated in the summary.
Confirm which columns contain hospital, destination, service, hours, and week-ending date, and verify that the week-ending values in the source data use the same date or text format as the summary criteria.
Use COUNTIFS and SUMIFS for each summary calculation
Use this solution when the source data is organized in consistent columns and the summary needs counts and hour totals for each week.
COUNTIFS counts rows that meet every supplied condition. SUMIFS adds the hours range for those same conditions. Use "<>Reablement" as the service criterion when the calculation must exclude Reablement records.
On the summary sheet, select cell B2 and enter =COUNTIFS(Sheet1!$A$2:$A$15,"NPH",Sheet1!$C$2:$C$15,"Home",Sheet1!$D$2:$D$15,"Reablement",Sheet1!$H$2:$H$15,$A2). This counts NPH records sent Home for the week-ending value in A2 where the service is Reablement.
Select cell C2 and enter =COUNTIFS(Sheet1!$A$2:$A$15,"NPH",Sheet1!$C$2:$C$15,"Home",Sheet1!$D$2:$D$15,"<>Reablement",Sheet1!$H$2:$H$15,$A2). The <> operator excludes Reablement.
Select cell D2 and enter =SUMIFS(Sheet1!$F$2:$F$15,Sheet1!$A$2:$A$15,"NPH",Sheet1!$C$2:$C$15,"Home",Sheet1!$D$2:$D$15,"Reablement",Sheet1!$H$2:$H$15,$A2). This adds hours from column F for matching Reablement records.
Select cell E2 and enter =SUMIFS(Sheet1!$F$2:$F$15,Sheet1!$A$2:$A$15,"NPH",Sheet1!$C$2:$C$15,"Home",Sheet1!$D$2:$D$15,"<>Reablement",Sheet1!$H$2:$H$15,$A2). This adds hours for matching services other than Reablement.
Select cells B2:E2, drag the fill handle down, or use Home > Fill > Down to calculate each row using the week-ending value in column A.
Calculate Multi-Criteria Counts and Weekly Totals in WPS Spreadsheet
WPS Spreadsheet supports COUNTIFS and SUMIFS, allowing you to build a weekly summary by hospital, destination, service, and date while excluding a specific service with a comparison criterion.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and open the workbook containing the source and summary sheets.
- 2. Enter the formulas: Use COUNTIFS for the two count columns and SUMIFS for the two hour-total columns, referencing the week-ending cell in column A.
- 3. Copy the formulas: Select the completed formula row and drag the fill handle down to calculate all required weeks.
Frequently Asked Questions
Which WPS Spreadsheet function should I use to count rows matching several conditions?
Use COUNTIFS. Add a range and criterion pair for hospital, destination, service, and week-ending date, such as COUNTIFS(Sheet1!$A$2:$A$15,"NPH",Sheet1!$C$2:$C$15,"Home",Sheet1!$D$2:$D$15,"Reablement",Sheet1!$H$2:$H$15,$A2).
How do I exclude Reablement from a COUNTIFS or SUMIFS calculation?
Use the criterion "<>Reablement" in the service criteria pair. This counts or sums rows whose service is not exactly Reablement.
Why does SUMIFS return zero even though matching records exist?
Check that the hours range contains numeric values, all ranges have the same row count, and the week-ending dates in the source and summary use matching date values rather than different text formats.
Can I use a cell instead of typing NPH or Home in the formula?
Yes. Replace the typed criteria with references such as $G$1 for hospital or $G$2 for destination. This lets you change the summary criteria without editing each formula.
