The SUMIF function allows you to analyse data based on a single criteria.

The use of SUMIF allows for the data to be analysed without the need to sort, reorganise or split the raw data.


In the above example we have queried the above list of data by three separate criteria.  The first example looked up the Provider and returned the sum of all costs associated with the “On a Roll” provider.

The second example queried the first column and sums all data that has a Spec code of “150”.

The third example is little more powerful as it only sums costs are are above £200.


Structure of SUMIF

=SUMIF(range to be analysed, criteria, range to sum)

Range to be analysed – this range is analysed by the criteria in the function

Criteria – the value you require

Range to sum – the items that should be SUM to return values required