Cdn.ymaws.com



This session will guide you through how to use the SUMIFS Excel formula to combine and align 10 years of account balances, both budget and actual.Gather your reportsRun four User Selected Budget Reports using “Download Excel File” format:User Selected Budget report for budget years 2008-2012 (ascending order by year)User Selected Budget report for budget years 2013-2017 (ascending order by year)User Selected Budget report for actual years 2008-2012 (ascending order by year)User Selected Budget report for actual years 2013-2017 (ascending order by year)Combine your reports into one WorkbookCombined all documents in 1 excel filePress and hold Ctrl while clicking on “sheet 0” from one workbook and drag it into the other workbook. Rename the tabs as you pull them into the workbook (Bud 08-12, Bud 13-17, Act 08-12, Act 13-17).Rename column headings to reflect the year they represent so you know what data you are working bine your dataMake a copy of the “Bud 08-12” tabPress and hold Ctrl while clicking on the tab and dragging it to the right of existing worksheets. Rename the tab Comparative.Delete the extra columns leaving only “AccountNumber, AccountDescription, and the 5 columns containing the amounts.Enter in your remaining budget and actual headers (2013 Budget through 2017 Actual) in cells H1 through V1 on the Comparative tab.Populate the remaining budget and actual columns using the SUMIFS formula. Use this so you don’t get the #N/A values as you would if you were using VLOOKUP when an account doesn’t exist.The Microsoft Excel SUMIFS function adds all numbers in a range of cells, based on a single or multiple criteria.SUMIFS Formula Syntax:=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2, ... criteria_range_n, criteria_n] )sum_rangeThe cells to sum criteria_range1The range of cells that you want to apply criteria1 against.criteria1It is used to determine which cells to add. criteria1 is applied against criteria_range1.criteria_range2, ... criteria_range_nOptional. It is the range of cells that you want to apply criteria2, ... criteria_n against. There can be up to 127 ranges.criteria2, ... criteria_nOptional. It is used to determine which cells to add. criteria2 is applied against criteria_range2, criteria3 is applied against criteria_range3, and so on. There can be up to 127 criteria.For this example, this is how to read/interpret the formula:=+SUMIFS('Bud 13-17'!M:M,'Bud 13-17'!$A:$A,Comparative!$A2)Give the sum of this (2013 budget column in Bud 13-17 tab) when you look at this column (column of all account numbers from the Bud 13-17 tab) and find this account (specific account # from Comparative tab).** you will notice the use of “$” in the formula. This invokes ‘absolute references’. When you drag/fill the formula into other cells, it will continue to use the same column and/or row. If the $ is not before a cell or column reference, the formula is using ‘relative references,’ meaning the formula reference will change relative to where you copy the formula to. The F4 key, pressed after you select/click on your column or cell, will toggle through absolute/relative references.If you don’t use absolute/relative references appropriately, you will have to manipulate your formula as you copy it to other rows and columns.In the Red, you should be using relative references. As you copy the formula to other columns, you want the reference to adjust left or right relative to the location you copy the formula to. You want to be able to reference column M initially (2013), but then as you copy the formula to columns to the right, you want it to reference other years (2014-2017).In the Blue, you are indicating that no matter whether you fill the formula to the left or right, the formula should always refer to Column A from the Bud 13-17 worksheet. In this example, you want the formula to always reference the column containing the account numbers.In the Orange, you are using both absolute and relative references. The absolute reference is on the column, indicating that no matter whether you fill the formula to the left or right, it should always refer to column A. You use a relative reference on the row indicating that when you fill the formula up or down, it should refer to the row relative to the new formula’s location (A3, A4, A5….)Enter the following formula into cell H2 (2013 Budget):=+SUMIFS(‘Bud 13-17’!M:M,‘Bud 13-17’!$A:$A,Comparative!$A2)ii. Copy the formula to the right to populate the formula in cells I2 – L2 (budget years 2014-2017)iii. Copy the formulas in H2 – L2 to the bottom of your account series.Check that you’ve included all new accounts:We know that all of the accounts for Budget Years 2008-2012 are included because IFS would have included them in your report and we made a copy of that worksheet to begin our comparison.On the Bud 13-17 worksheet, verify that all accounts are included in your Comparative worksheetHide your irrelevant columns to make your worksheet easier to work with; leave columns A, H and M-Q visible. In column U, use the SUMIFS function against the Comparative worksheet to see what data is NOT included (opposite of what was done in step 2C), and include a comparison at the end (-cell M2) to see whether the amounts match. Enter the formula in cell U2:=+SUMIFS(Comparative!H:H,Comparative!$A:$A,'Bud 13-17'!$A2)-M2If the answer is $0, then the amount on the Comparative worksheet matches your source data (Bud 13-17). If the answer is not $0, you need to add it to your Comparative worksheet. Copy the formula to columns V-Y for years 2014 through 2017 using the fill handle.Hover over the bottom corner of the cell with the formula. Once your cursor turns into a “+” hold down your left mouse button and drag the formula into adjacent cells.Apply a filter (in the Data Ribbon OR Ctrl + Shift + L) to the data in your validation columns. Starting with Column U, filter the data to show only amounts that are not $0.Copy and paste the accounts/account descriptions shown at the bottom of column A in your Comparative worksheet (do this as two separate copy/paste actions or you will paste in the hidden columns)**Filter tip: if you copy and paste filtered data, only the data you see will be pasted into the new location. When you hide rows of data all of the information copied (including hidden rows) will be pasted into the new locationOn the Comparative sheet, populate (fill down) the formulas in the columns for your newly added accounts. You may also want to include $0 in your 2008-2012 Budget columns. Go back to column U and check your filter to ensure that you only see $0 (or repeat step 4.B.vii). Repeat steps v. through viii for columns V through Y.Repeat step B for your Act 08-12 and Act 13-17 worksheets. Sort your data (if needed)Select all of your data and sort it by account number.*Be wary of formulas unless you paste values prior to sorting. If you choose to keep the formulas in while sorting, its best to re- “fill down” after you sort the data to ensure that account balances are still lining up.Other ideas for the SUMIFS Formula:Moving data from one spreadsheet to anotherSubtotaling dataIf you run an account activity for all departments for object code 6411 and now you want to know how much was spent by each departmentOR If you want to know how much was spent within an account for a particular vendorSummarizing your state payment reportWildcard (*) can be used in reference cells ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download