'%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 Return Excel Data Based on Location, Month, Day, and Year
Question details
The user needs to retrieve daily sales amounts from a Data sheet into a Totals sheet by matching four specific criteria: location ID, month, day, and year.
- Product
- Microsoft Excel
- Device & OS
- Mac
- Scenario
- Consolidating and summarizing data across multiple worksheets using multiple match criteria for detailed reporting.
- Observed behavior
- The user requires a multi-criteria formula to accurately extract and aggregate totals based on exact matches for location and separated date components.
Ensure that your source data is formatted as an Excel Table for easier referencing, and verify that the location IDs and date formats match exactly between your Data and Totals sheets.
Use the SUMIFS Function to Match Multiple Criteria
The most efficient way to return aggregated data based on multiple conditions (location, month, day, year) is by using the SUMIFS function.
The SUMIFS function adds all of its arguments that meet multiple criteria. By structuring your formula to check the location ID, month, day, and year columns independently, you can easily pull the correct amounts into your summary sheet without complex array formulas.
Open your Totals sheet and click on the cell where you want the calculated sales amount to appear.
Type `=SUMIFS(` to begin the formula. The syntax requires the sum_range first, followed by pairs of criteria_range and criteria.
Enter the formula tailored to your table. For example: `=SUMIFS(Data[Amount], Data[Location], $A2, Data[Month], $B$1, Data[Day], B$1, Data[Year], $B$2)`. Ensure you adjust the structured references to match your workbook's actual table and column names.
Use absolute references (like `$A2` for the location column and `$B$1` for the month row) so that the references remain locked correctly when you copy the formula to other cells.
Press Enter on your keyboard to calculate the result. Click and drag the fill handle at the bottom-right of the cell to apply the formula across your entire summary table.
Easily Calculate Multi-Criteria Data with WPS Spreadsheet
WPS Spreadsheet offers full support for advanced multi-criteria functions like SUMIFS, allowing you to efficiently retrieve and calculate data based on location, dates, and other custom conditions without any hassle.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your workbook containing the Data and Totals sheets.
- 2. Navigate to the Totals sheet: Click on the destination cell where you want to display the matched data.
- 3. Insert the SUMIFS function: Go to the 'Formulas' tab on the top ribbon, click 'Insert Function', and search for 'SUMIFS'.
- 4. Configure function arguments: In the dialogue box, input your sum range (the Amount column) and define your criteria ranges (Location, Month, Day, Year) along with their corresponding target cells.
- 5. Confirm and fill: Click 'OK' to apply the formula, then drag the fill handle to populate the rest of your summary table.
Frequently Asked Questions
Can I use SUMPRODUCT instead of SUMIFS for this task?
Yes, SUMPRODUCT can also be used to evaluate multiple criteria, which is especially useful in older Excel versions. However, SUMIFS is highly recommended because it calculates faster and its syntax is generally more straightforward for simple multi-condition sums.
What if my data has a single date column instead of separate month, day, and year columns?
If your source data contains a full date column, you can simplify the SUMIFS formula by matching the date directly. You can construct the target date in your formula using the DATE function, for example: `=SUMIFS(Data[Amount], Data[Location], $A2, Data[Date], DATE($B$2, $B$1, C$1))`.
Why is my SUMIFS formula returning a #VALUE! error?
A #VALUE! error in SUMIFS typically occurs if the sum_range and the criteria_ranges are not the exact same size. Ensure that all referenced columns (e.g., Data[Amount] and Data[Location]) cover the exact same number of rows.
Does the SUMIFS formula work smoothly on Excel for Mac?
Yes, the SUMIFS function is fully supported on Excel for Mac. It operates exactly the same as it does on the Windows version, including full support for structured table references.
