How to Use Excel SUMIFS to Total Data by Date and Shift
Question details
The user needs to summarize worksheet data based on two specific criteria: the date and the work shift, adding multiple matching records together.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a summary table that calculates total values (e.g., square meters) for specific dates and shifts from a master data sheet.
- Observed behavior
- Multiple entries for the exact same date and shift need to be successfully combined and calculated into a single total under the corresponding shift column.
Ensure your source data is organized in clean columns without merged cells, and verify that the date and shift formatting in your master data exactly matches the criteria formats you plan to use in your summary table.
Use SUMIFS with Custom Formatted Headers
Modify the column headers to be numeric and use the SUMIFS function to calculate totals based on both date and shift criteria.
The SUMIFS function is designed to add values based on multiple conditions. By keeping the shift column headers as actual numbers rather than text, you can easily reference them in your formulas while still displaying them nicely as 'Shift 1', 'Shift 2', etc., using Excel's custom formatting feature.
In your summary table, replace text headers like 'Shift 1', 'Shift 2', and 'Shift 3' with the simple numeric values 1, 2, and 3.
Select the numeric header cells, press Ctrl+1 to open the Format Cells dialog, navigate to the 'Custom' category, and enter `"Shift" 0` in the Type box. This displays the headers as text while keeping the underlying values numeric for formula references.
In the first result cell (e.g., for Date 1 and Shift 1), enter the formula: `=SUMIFS('Gs Table'!$Z:$Z, 'Gs Table'!$A:$A, $A5, 'Gs Table'!$B:$B, B$4)`. This formula sums the values in column Z where the date in column A matches cell $A5 and the shift in column B matches cell B$4.
Select the cell with the formula and use the fill handle (the small square at the bottom-right corner) to drag the formula across the other shift columns and down through the dates to populate the entire summary table.
Easily Summarize Complex Data with WPS Spreadsheet
WPS Spreadsheet fully supports advanced functions like SUMIFS, making it incredibly easy to analyze large datasets by multiple criteria such as dates and shifts. It offers the same powerful calculation engine as Microsoft Excel, completely free.
- 1. Open your dataset: Launch WPS Spreadsheet and open your existing .xlsx data file containing the master records.
- 2. Format your table headers: Highlight your shift headers, right-click to choose 'Format Cells', and apply the custom format `"Shift" 0`.
- 3. Apply the SUMIFS function: Type your SUMIFS formula directly into the summary cell, utilizing mixed references, and drag the fill handle to automatically calculate all totals.
Frequently Asked Questions
What is the difference between SUMIF and SUMIFS?
SUMIF can only evaluate a single condition or criteria, whereas SUMIFS is designed to evaluate multiple conditions (like checking both a specific date and a specific shift) simultaneously.
Why does my SUMIFS formula return a #VALUE! error?
This usually happens if the sum range and the criteria ranges are not exactly the same size. Ensure all ranges in your formula cover the exact same dimensions (for example, if you use $A$2:$A$100 for criteria, you must use $Z$2:$Z$100 for the sum range, or just use entire column references like $A:$A and $Z:$Z).
Can I use text headers instead of numeric headers for the shifts?
Yes, but your formula criteria will need to match the text exactly. If your raw source data uses numbers for shifts, it is much easier to use numeric headers with custom formatting in your summary table to ensure the SUMIFS criteria match perfectly.
How do I handle empty cells in my criteria ranges with SUMIFS?
SUMIFS treats empty cells as blank or zero. If you need to sum records specifically where a certain column is completely blank, you can use "" (empty double quotes) as your criteria condition for that range.
