ArrayFormula1

Use an Excel Array Formula to Do Multiple Calculations at Once

Array formulas can be challenging to understand but they’re worth learning as they can create significant efficiencies for your spreadsheets. Below, I’ll show you how you can do two calculations within a single array formula in Excel.

Using an array to categorize and sum data

To demonstrate how to do multiple calculations, I’m going to use credit card transactions as an example:

Credit card transactions showing descriptions and amounts.

Suppose in column A you have the first letter indicating whether it’s a Visa, Mastercard, or Amex. The numbers afterward could be authorization codes. And then in column B, you have the total dollar amount of the transaction. Without the use of an array, what you might end up doing is creating another column that would use the description in column A to determine the card type. Then, you could use a SUMIF function to calculate the sales volume by card type. Here’s how that might look:

Summarizing credit card transactions using a SUMIF function.

This is an approach I’d expect most people to use. It’s perfectly fine but if you’re not able to add an extra column or need to make your formula more efficient, that’s where an array can be helpful.

Through the use of an array, you don’t have to create that extra column. You can determine the card type and also sum the data all in one cell. Let’s start by calculating all the Visa transactions.

First, we’ll start by determining the card type — this process would have been the same regardless of whether you’re using an array or a SUMIF calculation. And to do this, we need to use the LEFT function to grab the first letter and determine if it is a ‘V’ to indicate Visa. This is how the formula looks like:

LEFT(A2:A30,1)=”V”

Right now, we can’t really use this on its own, it’ll just return TRUE or FALSE values. What we can do is put this formula inside of an IF function and using it as the first argument:

=IF(LEFT(A2:A30,1)=”V”

In the second IF argument, we’ll want to return the values in case the condition is true, and that V is the first character of the corresponding value in column A. In such a case, we just want to return the values that are in column B:

=IF(LEFT(A2:A30,1)=”V”,B2:B30

And if the value in column A doesn’t start with a V, then, in that case, we just want the value to be blank:

=IF(LEFT(A2:A30,1)=”V”,B2:B30,””)

I use blanks rather than a 0 value because it’s cleaner, and you’ll see why that is when I evaluate the formula further down.

The formula will now return a list of values, ignoring those which don’t start with a V in column A. All that’s left now is to sum those values. To do that, we’ll simply wrap the function above inside the SUM function and freeze the cells:

=SUM(IF(LEFT($A$2:$A$30,1)=”V”,$B$2:$B$30,””))

And here’s the result:

Summarizing credit card transactions through the use of an array formula.

If you’re using an older version of Excel, you may need to use CTRL+SHIFT+ENTER to turn this into an array formula. But on newer versions, it’s no longer necessary. I didn’t need to in my case, and I can show you that it still calculates as an array. If I run the EVALUATE FORMULA button on the Formulas tab, you’ll see that it is evaluating each cell and pulling the first letter from each of the values in column A:

Evaluating the first part of the array formula.

Then it converts that into a series of TRUE or FALSE depending on whether those values are equal to ‘V’:

Evaluating the second part of the array formula.

And then it returns the corresponding values if they are TRUE, and “” if they are FALSE:

Evaluating the third part of the array formula.

Notice how easy it is to separate the numbers out from the blank cells. Had I used zeros rather than blanks, it would be a bit more difficult to discern which were Visa transactions and which were not. Anytime you can hide zero values, your data is always a lot cleaner.

The last step of this formula involves just summing all the values, which gives us our total. This is a much simpler approach than having to create another column for determining the card type. You can also use this with other functions. If we wanted to get the average Visa transaction, we could just sub out the SUM function with the AVERAGE function. Arrays are very powerful and can do some incredible things.


If you liked this post on how to use an Excel array formula to do multiple calculations at once, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

lasvegas1

How to Calculate Cumulative and Year-to-Date Totals in Excel

Whether you’re tracking sales or costs in excel, it’s important to capture not just your monthly totals but your cumulative year-to-date amounts as well. And to do that in excel, you’ll need to calculate a cumulative sum. Ideally, you’ll want to see a current month’s total alongside the year-to-date figure. Below, I’ll show you how to do that as well as how to make cumulative totals work with multiple years.

Calculating the current month and cumulative sums

First thing’s first, let’s start with a data set. This time around, I’m going to pull the monthly tourist information for Las Vegas. This year, that could prove to be interesting given the impact of COVID-19 on tourism in the city. Here are what the numbers looked like for 2019:

Las Vegas visitor data in Excel.

If we wanted to calculate the total visitor volume it would be as simple as the following formula:

=SUM(B:B)

However, if we want the cumulative totals then we can’t just grab the entire column. Instead, we’ll need to add another column that has the cumulative amounts for each month. The formulas will still involve the SUM function but they will need to be from January up until the current row. Here’s what the formulas look like:

Las Vegas visitor volume cumulative sum totals.

The formulas for column C are shown in column D. The key here is freezing the first cell (B2) so that as you copy the formula down in C2, it won’t move while the other cells will.

Calculating cumulative values isn’t too complicated, but it’s a bit trickier when your data set spans multiple years.

Calculating the cumulative sum when working with multiple years

The above scenario works well if you have just one year. But it won’t work if you decide to add next year’s data without resetting the formula. Here’s how it would look if we added the 2020 data:

Las Vegas visitor volume cumulative sum totals for multiple years.

As you can see, it just keeps on adding on to the previous year’s data, which is not what we want.

There are multiple ways that you can calculate the cumulative sum per year and so that the calculation resets on its own. Let’s start with the easiest route: adding an extra column for the year. Using the YEAR function we can extra what the year is in column A. Then, rather than using the SUM function, we will use the SUMIF function to do the cumulative count, but only if the year is the same:

Las Vegas visitor volume cumulative sum totals for multiple years with a sumif function.

The logic similar to the earlier formula, we’ve just added a condition where the year in column C has to match the year that specific row belongs to. That’s why once we hit 2020, it resets. For this to work, we still need the months to be in order.

Another way that you can calculate the cumulative total without a helper column is by using an array:

Las Vegas visitor volume cumulative sum totals for multiple years with an array.

We need to evaluate every cell to see if it relates to the correct year, and if it does, it gets included in the range to sum. The array allows us to do two calculations in one: an IF calculation embedded within a SUM calculation which doesn’t require the helper column.

A big advantage of having multiple years on your data set rather than separating them out is then you can put them into a pivot table and create a pivot chart that helps plot both of them:

Las Vegas visitor volume shown on a chart.

From this, we can see that there was a sharp drop off in March due to the outbreak of COVID-19 and that the cumulative figures are now well under 2019’s numbers. By having both cumulative and monthly totals available, we can display them both on one chart that helps to summarize the information quickly and easily.


If you liked this post on How to Calculate Cumulative and Year-to-Date Totals in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

sum calculation

Using Summation Formulas (SUM, SUMIF(S), SUBTOTAL)

I have saved this sample file here if you would like to look at the data set and have a closer look at how the formulas work.

In this post I will cover some simple summation formulas and when best to use them. I have a sample data set that has stores, customers, and total sales.

sum calculation

Summing Total Sales


If I just wanted to total all the sales then the SUM function would accommodate this easily. There is even a summation button on the Excel ribbon to easily do this.

sum ribbon button

It is on the right hand side of the Home tab in the Editing group. It will try to determine which cells you want to sum but you can change this range to what you need.

Since my values are in column C my formula would be as follows:

=SUM(C:C)

Pretty straightforward, all you need is the column that you want to sum (or specific range if you don’t want everything in the column). This returns the grand total of $256,129 from my data set. Another way I could find out the total is if I just highlight the entire column.

sum column

The sum will show in the bottom right corner of my screen in the status bar.

sum status bar

If I right click on any of this area I get the following menu:

customize status bar

If you didn’t see the sum in your status bar this is where you can add it. In my example I could select Minimum and Maximum and then those values would also be calculated for any range that I highlight and show up in the status bar. This makes it easier if you quickly want to see an average, total, min or max by just highlighting a range without having to type a formula each time.

Summing Single Store Sales

But now suppose I just wanted to know the sales of Store A. I could filter the data set, select the column, and see the total in the status bar. But that is a bit tedious to repeat each time if I wanted to see sales by each store. In this case, I could use the SUMIF function.

My formula would look like this:

=SUMIF(A:A,”Store A”,C:C)
      

The first argument, column A, specifies what range I want to look at; the second argument, “Store A”, is what criteria I am looking for; and column C is the final argument, where I want to pull the values from. This formula is saying to look in column A for a value of Store A and add only those related amounts in column C.

Instead of a static reference for Store A I could reference a cell instead, and that would make it easier to apply this formula to multiple stores without having to change the name manually each time.

In the above example assume my store name is in cell E6. If I have the store names going from cell E6:I6 then I could use the following formula:

 =SUMIF($A:$A,E6,$C:$C)

You will notice I have frozen some of the cells as well. This will allow me to move my formula across without the ranges changing. Below are what my results look like:

sumif calculation function

Summing Sales by Customer and Store

Now, consider a scenario where I wanted to do a summary of sales by stores and customers. Here I can use the SUMIFS function (only available to Excel 2007 and newer versions). In the formula below I have stores in cells G12:J12 and customers in cells F13:F16:

 =SUMIFS($C:$C,$A:$A,E$12,$B:$B,$F13)

I can copy this formula and it will be updated based on what store and customer intersect at that point. The summary will look as below:

sumifs formula calculation

Unlike with the SUMIF function, the first argument here is the range I want to sum, which is column C. The next argument is the range for my first criteria to look up, column A, followed by the criteria I want to match, which in the first formula is cell E12 (Store A). The next two arguments relate to the next criteria range which is column B for the customers and the customer to be matched which in the formula is cell F13 (Customer A).

With the SUMIFS function you can add more criteria than just two. Simply just add another comma and in the next argument specify the range, followed by the criteria. You can keep adding to it as you need.

How the SUBTOTAL Function Works

Unlike the SUM function, the SUBTOTAL function will perform a calculation based on the filters you have applied (if you have none, it will perform a calculation on all the data in the range). The SUBTOTAL function also has an added argument to tell it what type of calculation it should do:

subtotal functions

In the below formula, I am going to total column C again, as I did in the SUM formula initially. However, the additional argument I need in the SUBTOTAL function is defining the calculation. Since I am going to just do a sum, I will set the argument to 9. My formula looks as below:

 =SUBTOTAL(9,C:C)

Now, if I were to use filters to show only Store A and Customer A, the SUBTOTAL formula would return the same result as the SUMIFS function did for the intersection of Store A and Customer A.

subtotal filters

My formula returns a value of $10,796 with the above filters, which matches the result from the SUMIFS formula. The SUBTOTAL function is useful when you are always using filters since it will take those filters into account as opposed to the SUM function which will ignore them. If you don’t use filters then it won’t make a difference.