Maths – Sumifs() formula

Sumifs()

• SUMIFS is a relatively new formula that lets you add together values based on multiple criteria. It can be used with a single criteria as well and therefore there is no need to use the older Sumif() formula – advisable if you are new to these type of formulas as the parts (or arguments) of the SUMIFS and SUMIF formulas work in a different order which can be confusing when you move from one to another.
• The formula has the structure =SUMIFS (range of cells to add, Criteria range1, criteria1, [Criteria range2], [criteria2], …)
  • ‘Range of cells to add’ (sum range in excel speak) is a single column of values where only those values that meet the criteria in the rest of the formula are added together
  • ‘Criteria range’ is a single column of cells which contains items that can occur more than once e.g. a division name, product name, month etc.
  • ‘Criteria’ is a cell containing the specific item that you are looking for in the ‘criteria range’. In a summary table this would normally be a row or column heading.
  • ‘Criteria range’ and ‘Criteria’ must always be paired up. You can use many such pairings in your formula but in my experience you will rarely need more than two (the most I have ever used is four criteria).
• The dialogue box for this formula is useful and one of the few that I nearly always use (as opposed to typing the formula directly into the cell).

In the example below

• Downloaded sales data is shown from row 14 downwards with each line showing the division making the sale, the product sold, the month of the sale and the value of the sale
• The summary table is in Cells B3:N7 and shows the results for a particular division by product and by month
• The division is selected from the data validation list set up in Cell B2.
• The SUMIFS() dialogue box is shown and from this we can see that
  • The sum_range is looking at the values cells E15:E150 (note the data only goes to row 127 but the formula goes to a lower row to allow for any additional data being added at a later stage)
  • The criteria range 1 is looking at cells B15:B150 which contains the division data
  • Criteria 1 is the division selected in cell B2
  • The ‘criteria range 2’ is looking at cells C15:C150 which contain the product data
  • Criteria 2 is the product heading in column B
  • The ‘criteria range 3’ is looking at cells d15:C150 which contain the product data
  • Criteria 3 is the month heading in Row 3
  • NB in the dialogue box only two criteria are visible at any one point in time, Use the scroll bar on the right on the dialogue box to vary which criteria are visible)
• The formula bar shows the formula as it would appear in the cell
• The formula can be copied to each cell in the summary table and in the example shows sales for West division for each product by month e.g. in June 2016, West sold 181 units of the blue product.