Sum of Column B, based on values in Column A

Use an array formula to calculate selected values in one column, based on parameters in another.

In Excel, it is possible to calculate the SUM of values in one column, based on the values of another. In the following example, I have a formatted table (named ‘Loans’) which shows how much money has been borrowed by whom over time:

Whilst deriving a SUM value for the total amount owing is easy (=SUM(Loans[Amount])), selectively deriving a SUM value for only those loans made by Jane (for instance) is more challenging. To solve this, an array formula is used.

An array is a series of data in a row, column, or a combination of rows and columns. An array formula performs calculations on the data in one or more arrays, returning either a single or multiple results.

In this particular case, the following array formula will determine which loans have been made my Jane and determine their total:

=SUM(IF(Loans[Name]="Jane",Loans[Amount]))

This formula says “Calculate the SUM of the Amount column IF the Name is ‘Jane’, otherwise ignore the value”.

Because this is an array formula, it won’t work simply by entering it into the formula bar and pressing enter. The array formula is activated by pressing Ctrl + Shift + Enter. You will then notice that the curly braces are applied to the formula, indicating that it’s an array:

{=SUM(IF(Loans[Name]="Jane",Loans[Amount]))}

Using the above formula, it can be determined that Jane has borrowed $18.40.

Have Your Say

Δ