Skip to content

Sum of Column B, based on values in Column A

G28th November 2014

CNo Comments

TExcel, Microsoft Office

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:

Name Date Amount
Jane 23 October 2014 $3.25
Sarah 30 October 2014 $6.20
Jane  4 November 2014 $10.25
Jane  20 November 2014 $4.90
Lucy 28 November 2014 $1.50
Sarah 28 November 2014 $9.45

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.

   

Comments:

No comments have yet been submitted. Be the first!

Have your say:

The following HTML is permitted:
<a href="" title=""> <b> <blockquote cite=""> <code> <em> <i> <q cite=""> <strike> <strong>

Comments will be published subject to the Editorial Policy.