Using SumIF to add up specified values in Excel

Part of the process of learning about Excel is overcoming the fear of new terminology, especially when it is linked to functions. Today’s term is SumIF.  I use Autosum all the time, so why would I need to use SumIF?

Well, Autosum adds up every cell that I select in a row or column. Great for totalling invoice totals, or expenditure for a month, or adding up items in inventory lists.

But what if I only want to add up items over a specific value?  For example, if I have the authority to make payments up to the value of £250 within a list of expenditure – could I add up the items that were up to and including £250 in value?

I can do this if I use SumIF – as it is designed to add up a range of cells only when the criteria I specify is met.

The format I need to use is =SUMIF(range, criteria)

In my example, the SumIF formula I will use is =SUMIF(B2:B8,”<=250″), as my range is B2 to B8, and I want to see invoices authorised from less than or equal to the value of £250).

SumIF-visual-basic-excel-training
The formula bar shows the SUMIF formula, with the range and criteria specified. The result is shown in cell E2.

This is a simple example where SumIF can calculate the totals authorised by the admin department and those authorised by the Manager.

SumIF can do far more than this, and it is worth exploring the options it gives you.

There are lots of uses of the SUMIF function that can really transform your Excel worksheets.    From Introduction to Advanced, PowerPivot to Visual Basic Excel Training, our courses cover the range skills that can boost your performance at work.
Take a look at what Excel can do for you at https://www.stl-training.co.uk/microsoft/excel-training-london.php