{"id":150,"date":"2016-10-27T11:44:05","date_gmt":"2016-10-27T16:44:05","guid":{"rendered":"http:\/\/www.iqaccountingsolutions.com\/blog\/?p=150"},"modified":"2016-10-27T11:46:54","modified_gmt":"2016-10-27T16:46:54","slug":"excel-conditional-totals-using-sumif","status":"publish","type":"post","link":"https:\/\/www.iqaccountingsolutions.com\/blog\/excel-conditional-totals-using-sumif\/","title":{"rendered":"EXCEL – CONDITIONAL TOTALS USING SUMIF"},"content":{"rendered":"

Most people know how to use Excel to add the numbers in a column.\u00a0 But what if you want to add only the numbers in a column that meet a certain criteria?\u00a0 There are a couple of ways that can be accomplished, (aside from manually selecting cells yourself) but the simplest is usually to use the SUMIF function.<\/span><\/p>\n

For our example, lets say you have a spreadsheet of sales sorted by date and you don\u2019t want to change that.\u00a0 But you need to get totals for each sales region, and all regions are mixed together in that same list.\u00a0 If the amount is in column D, the sales region is in column E, and our data is in rows 2 through 25, here is how you can get the totals you need.<\/span><\/p>\n

First, go to the cell where you want the total. Then click the function wizard button. (It is usually just above the column headings , next to the formula bar and looks like a small F with an x after it.) Type SUMIF<\/span><\/em> in the search box and press Enter<\/span><\/em> (or click the Go<\/span><\/em> button) and then click OK<\/span><\/em>.\u00a0 That will open a window that will help you build the formula.<\/span><\/p>\n

At Range<\/span><\/em>, you want to enter the range of cells that hold the information that you want Excel to evaluate to determine if the sales amount should be included in your total.\u00a0 In this example, that would be rows 2 through 25 of column E (the sales region column). So you can either highlight those cells with your mouse, or type E2:E25 in the Range box.<\/span><\/p>\n

Criteria<\/span><\/em> is where you tell Excel what to look for in the range we just gave it.\u00a0 Let\u2019s say that our sales territories are North, South, East, and West.\u00a0 So here, just type the word North in the Criteria box.\u00a0 You could also enter the location of a cell that contains the word North.\u00a0 If we typed North in the cell next to our total, we could have entered that cell\u2019s location in Criteria <\/span><\/em>instead of typing the word North.<\/span><\/p>\n

Sum_range<\/span><\/em> is the range of cells that contains the numbers that we wanted added (if they meet the criteria).\u00a0 So you can type D2:D25 here or highlight column D, rows 2 through 25 (the sales amounts) with your mouse.<\/span><\/p>\n

Notice that, to the right of each entry, Excel shows you the result of what you entered.\u00a0 And beneath that, the result for the formula.\u00a0 When you click OK, the formula will be entered in the cell for you.\u00a0 You will now have a total of all sales for the North region.<\/span><\/p>\n

I used a very simple criteria in this example, but you can use more complex comparisons.\u00a0 For example, entering >P<\/span><\/em> in the criteria would total the sales if the region comes after P alphabetically (South and West in our example). So don\u2019t be afraid to experiment and see what kind of spreadsheet problems you can solve using SUMIF.<\/span><\/p>\n