How to Use Excel SUMIFS Formula for a Name and Date Range
Question details
The user needs to calculate total invoice charges for a specific person between the first and last day of a selected month using a formula.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Summarizing financial data, such as invoice charges, based on a specific individual's name and a particular month.
- Observed behavior
- Looking for the exact syntax to apply multiple criteria, including text matching and date boundaries, within a single SUMIFS function.
Verify that your date column is formatted as Date values rather than text, and ensure the sum range and criteria ranges are exactly the same size.
Use the SUMIFS Function Combined with the DATE Function
Apply the SUMIFS formula alongside the DATE function to create an accurate boundary for the start and end of a specific month.
The SUMIFS function allows you to add values that meet multiple conditions. By using the DATE function for your criteria, you avoid regional date formatting errors and ensure accurate calculations.
Determine the layout of your dataset. For example, assume Column H contains the charges (Sum Range), Column F contains the names (Criteria Range 1), and Column G contains the invoice dates (Criteria Range 2).
Select an empty cell where you want the total to appear. Click on the formula bar and enter the following formula: =SUMIFS($H:$H, $F:$F, "Donald", $G:$G, ">="&DATE(2024,1,1), $G:$G, "<"&DATE(2024,2,1))
In this formula, >=DATE(2024,1,1) sets the lower limit to the first day of the target month. <DATE(2024,2,1) sets the upper limit to the first day of the next month. Using the less-than operator (<) for the following month ensures all time stamps on the final day of the target month are included.
Press the Enter key on your keyboard to execute the formula. The cell will now display the total invoice charges for 'Donald' in January 2024.
Calculate Data Dynamically with WPS Spreadsheet
WPS Spreadsheet fully supports the SUMIFS function and provides a highly compatible, user-friendly environment for advanced data analysis. You can easily manage financial records, invoices, and large datasets with precision.
- 1. Open your dataset: Launch WPS Spreadsheet and open your existing invoice or financial tracking workbook.
- 2. Insert the SUMIFS formula: Click on your target cell, type =SUMIFS(, and use the formula bar to input your sum range, name criteria, and date boundaries.
- 3. Use Formula Prompts: As you type, WPS Spreadsheet displays a helpful syntax tooltip to guide you through the Sum_range, Criteria_range1, and Criteria1 parameters.
- 4. Drag to fill: If you are referencing cells for names and dates, simply click and drag the fill handle down to apply the formula to other rows instantly.
Frequently Asked Questions
Why does my SUMIFS formula return 0 when filtering by date?
This usually happens because the dates in your criteria range are stored as text instead of actual date values. Select your date column, right-click and choose 'Format Cells', then change the category to 'Date'.
Can I use cell references instead of the DATE function in SUMIFS?
Yes. Instead of typing ">="&DATE(2024,1,1), you can input your start date in a cell like A1 and use the criteria ">="&A1 in your SUMIFS formula.
What is the difference between SUMIF and SUMIFS?
SUMIF is used when you only have a single condition to check. SUMIFS is designed for multiple conditions, such as verifying a specific name AND a specific date range simultaneously.
Does WPS Spreadsheet support all Excel statistical functions?
Yes, WPS Spreadsheet supports all standard Excel formulas, including SUMIF, SUMIFS, COUNTIFS, and AVERAGEIFS, ensuring your existing Microsoft Office spreadsheets work seamlessly without any modifications.
