Consider the following table of data:
If we want to find the total of all the Lorries then we could use the SUMIF function:
E25 = SUMIF(A5:A20,”Lorries”, E5:E20)
Like so:
And likewise we could total all the red vehicles:
=SUMIF(C5:C20,”Red”,E5:E20)
However what about if we wanted the total of all the YELLOw Vehicles in London ? There are in fact several ways of doing this. We will consider one for now and look at the others in later articles.
The simplest way of achieving this is to add another column which concatenates the City and Vehicle colour for each row, and then do a SUMIF across this new column:
D5 = A5 & “ “ & C5
The operator & allows us to Concatenate two strings. It is a shorter alternative to the function CONCATENATE. We are inserting a space “ “ between the strings in Column A and C – to make it more readable. So copying down we have:
And then we just use SUMIF on the new column:
=SUMIF(D5:D20,”London Yellow”,F5:F20)
We have that:
The total is 54,800 – the sum of 45,000 and 9.800.
As I mentioned that there are other ways of other doing this – and we’ll look at them in future articles.
