SUMIF – Multiple Criteria
Excel’s SUMIF formulae allows you to obtain the sum of a range of values that meet certain criteria. For example:
If we wanted to obtain the total number of Cars then we could use SUMIF:
and if we wanted to obtain the total vehicles in Cambridge we could use:
However what would we do if we wanted the total number of all the cars in Cambridge ? We can’t use SUMIF as it only allows for one criteria to be selected, and we have two (City and Vehicle Type). The solution is to use SUMPRODUCT:
=SUMPRODUCT(–(Criteria 1),–(Criteria 2), Summation Values)
where Criteria 1 and 2 are the data selection values (i.e City = “Cambridge” and Vehicle Type =”Cars” and the Summation Values are the values that are being summed (Column D – the numbers of vehicles).
Excel will go through each of the cells in the range A5:A12 and evaluate those that equal “CAMBRIDGE”. For those that do, it assigns the value “TRUE”. For those that do not equal “CAMBRIDGE”, it will assign the value “FALSE”.
The – is a Logical Operator within Excel and is nothing to do with the normal minus operator. This operator converts all “FALSE” values to 0 and “TRUE” values to 1.
This enables us to use SUMPRODUCT in this way to evaluate multiple criteria: