In the second instalment of his overview of Excel’s summary functions, Simon Hurst delves further into the different capabilities of SUMIF, SUMIFS and array formulae for creating conditional sum arguments.
Adding up is one of the most common things to do in a spreadsheet. At its simplest, this might involve using the + operator to add up the contents of a couple of cells but, for anything more complicated, one of Excel's aggregate or conditional sum functions is likely to be necessary. When working with significant volumes of data, the best approach might be to use a PivotTable to summarise the data. Specific Excel functions are available to work with individual totals from a PivotTable or even directly from the underlying data. Adding is just one of a range of calculations that can be performed on a set of values and some of the functions that generate totals can also be used to perform these other calculations.
Excel summary series - summary
This series looks at different Excel functions and features that can be used for adding up and evaluating values. The previous article looked at simple sums and examined the more flexible aggregate functions introduced in Excel 2010, SUBTOTAL() and the AGGREGATE(). This article looks at various ways to extract summaries conditionally, for example to calculate the total sales made of a particular product from a detailed list of sales invoices.
Register with AccountingWEB.co.uk for free and log in to read instructions on the different methods covered, which include:
About Simon Hurst
Simon Hurst is the founder of technology training consultancy The Knowledge Base and is a past chairman of the ICAEW's IT Faculty.