How to Use SUMIFS to Total Specific Sales by Day in Excel
Question details
Calculate the total number of specific product sales on specific days using an Excel formula instead of a PivotTable.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- A user wants to sum data based on multiple criteria, such as product type and weekday, using a formula.
- Observed behavior
- The user needs to correctly apply the SUMIFS function to get accurate totals that match PivotTable results.
Ensure your data set is organized into clear columns with headers (e.g., Product, Weekday, Quantity) and that there are no extra spaces or hidden characters in your text criteria.
Use the SUMIFS Function for Multiple Criteria
The SUMIFS function adds all of its arguments that meet multiple criteria, perfect for summing specific products on specific days.
The SUMIFS function is designed specifically for scenarios where you need to filter data using more than one condition. Unlike SUMIF, which only handles a single criterion, SUMIFS can evaluate multiple columns simultaneously.
Locate the column containing the values to sum (e.g., Column C for Quantity), the first criteria column (e.g., Column A for Products), and the second criteria column (e.g., Column B for Weekdays).
Select an empty cell where you want the total to appear and type =SUMIFS( to begin the formula.
Complete the formula by entering the sum range followed by each criteria range and its specific condition. For example, to calculate Monday cake sales, type: =SUMIFS(C:C, A:A, "Cake", B:B, "Monday").
Press the Enter key. Excel will return the total sum of all sales that match both the 'Cake' and 'Monday' criteria.
Troubleshoot SUMIFS and PivotTable Discrepancies
Fix mismatches between your SUMIFS formula results and PivotTable totals by checking for data inconsistencies.
Calculate Complex Data with SUMIFS in WPS Office
WPS Spreadsheet fully supports Excel functions, including SUMIFS, allowing you to easily calculate totals based on multiple criteria like products and weekdays.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your .xlsx workbook containing the sales data.
- 2. Select the target cell: Click on the cell where you want to display the total sales.
- 3. Insert the SUMIFS function: Go to the Formulas tab, click 'Insert Function', search for SUMIFS, and input your sum range and criteria ranges.
- 4. View the result: Click OK or press Enter to instantly see the calculated total.
Frequently Asked Questions
What is the difference between SUMIF and SUMIFS?
SUMIF is used to sum values based on a single condition, whereas SUMIFS is designed to sum values based on multiple conditions, such as finding the total for a specific product on a specific day.
Why does my SUMIFS formula return a 0?
A result of 0 usually means there are no records in your dataset that match all of your criteria exactly. Check for typos, extra spaces, or hidden characters in your data cells and formula text.
Do the criteria ranges in SUMIFS need to be the same size as the sum range?
Yes, all ranges provided in the SUMIFS function must have exactly the same number of rows and columns. If they do not align perfectly, the formula will return a #VALUE! error.
Can I use wildcard characters with the SUMIFS function?
Yes, you can use wildcards like the asterisk (*) to represent any sequence of characters or the question mark (?) to represent a single character in your criteria. For example, using "*Cake*" will count "Chocolate Cake" and "Vanilla Cake".
