Excel Tips

Tips from The Finance Trenches: How to Use Variables in MS Excel SUMIF Formulas

Do you need to find the sum of a subset of cells in Excel spreadsheet that has multiple variables? Here, we’ll take a look at how adding a conditional variable (or variables) can help you get to your desired output – and we’ll also offer a few shortcuts along the way. Keep in mind, this approach simply builds on standard SUM formulas in MS Excel, where a range of cells is selected and the output is the sum of the results, and the formula can be copied across broad ranges of columns or rows.

First Step: Add a Variable

Let’s start with the following SUMIF formula:

=SUMIF(Variable_Range,Variable,Sum_Range)
Next, using the data from our example below, let’s say we need to find the sum of units sold in Asian countries. We would plug in the values as follows:

  • “Variable_Range” is the list of countries in column A
  • “Sum_Range” is the number of units in column D
  • “Variable” is in cell A24 (you can also replace the A24 with “Asia”, but that requires editing the formula if you want to find the results for “North America” or “Europe”).

So our equation then becomes:

=SUMIF(A8:A22,A24,D8:D22)

ms_excel.png

Shortcut Tip: If you have multiple columns of data, simply copy the formula to other rows by “locking” your variables and copying the formula across all columns. To demonstate using the formula above, our formula would then become =SUMIF($A$8:$A$22,$A24,D$8:D$22); where the column identifying “Region” (Column $A$) is locked for rows and columns, the Region to sum (Cell $A24) is locked for column, and the units (Column D$) lock the rows, but not the column – so that the formula can be copied to other rows. 

Next Steps: Add a Second Variable

Now let’s modify our formula to add a second variable, changing SUMIF to SUMIFS by adding the following elements:

=SUMIFS(Sum_Range,Variable_1_Range,Variable_1,Variable_2_Range,Variable_2)

* Note: this formula is not restricted to the number of variables, meaning you can add Variable3…..VariableX.

excel_sum_formulas.png

The key difference here with our IFS formula is that the sum range becomes the first portion of the formula, and the variable range and variable become the 2nd, 3rd, etc portions.

Variation: Use the AVERAGEIF Function

AVERAGEIF and AVERAGEIFS can be substituted in the formulas above to get the average of a single or multiple variable.

excel.png

Shortcut tip: Formulas to measure the number of occurrences can also use the IF and IFS function with the following formulas: COUNTIF(Variable_Range,Variable) and COUNTIFS(Variable_1_Range,Variable_1,Variable_2_Range,Variable_2).

We hope you found this tip useful! Are there any more reporting functions that you’re interested in streamlining? Don’t hesisate to contact us to let us know! Also check out our other recent Tips from from the Trenches:

Remember, 8020 Consulting can also assist you with complex accounting or finance matters that can make a significant and immediate impact on your bottom line. Take the first step now by telling us what you’re looking for.

Contact Us

Categorized in:

similar articles

Learn to think and approach problems like our financial consultants.

Financial Systems

Notes from the Field: Avoiding Surprises in ERP Stabilization Projects

This blog is based on my recent experience with a client that had implemented Microsoft Dynamics AX/Dynamics 365 for Finance and Operations. That enterprise resource planning (ERP) software offers benefits such as end-to-end system connectivity, resulting in profitability, transparency, and efficiency. It’s robust, but agile at the same time, and has a very familiar Microsoft… View Article

August 22, 2019Jackson Quach

Financial Planning & Analysis

Optimizing Your Price Increase Strategy

Price increases are a fairly controllable tactic for Finance teams looking to improve profitability. Ultimately, price increases can come in several forms (e.g., adjusting rebates or cost to serve) and may only apply to select customers. After collaborating with leadership, sales, and marketing teams to select the specific product/solution(s) for which to raise pricing, several… View Article

August 15, 2019Marco Moreno

Financial Systems

NetSuite SuiteAnalytics: Leveraging Built-In BI

While NetSuite has long been a leader in embedded analytics, Oracle clearly recognized the need for more robust reporting and analytics tools. As a proxy to and an improvement upon other third-party, plug-in solutions, Oracle developed its own solution natively embedded within its already industry-leading ERP. Netsuite SuiteAnalytics is a built-in analytics and reporting package… View Article

August 13, 2019Chris Moss

See All