Excel Math Formulas

Advanced SUMPRODUCT Function: Sum Multiple Criteria

What does it do?
It returns the sum of multiple criteria from the corresponding ranges or arrays
Formula breakdown:
=SUMPRODUCT((array 1 criteria) * (array2 criteria) * array values)
What it means:
=SUMPRODUCT((find my criteria in this array) * (find my criteria in that array) * return the values from the values array)

---

The SUMPRODUCT function is my favorite Excel function by a stretch!  You can create some powerful calculations with the SUMPRODUCT function by creating a criteria for a selected array.  For example, you can see how much sales your sales rep did in a particular region and for a particular quarter without having to create a Pivot Table.
It takes some practice to get comfortable with this function but when you master it, it opens up another Excel world!

In our example, we want to get the total sales of John in the North Region in Q1:

STEP 1: We need to enter the SUMPRODUCT function:

+SUMPRODUCT(

STEP 2: Create the criteria for the Sales Rep “John”:

+SUMPRODUCT((B15:B23=”john”)*

Create the criteria for the Region “North”:

+SUMPRODUCT((B15:B23=”john”)*(C15:C23=”north”)*

Create the criteria for the Quarter “1”:

+SUMPRODUCT((B15:B23=”john”)*(C15:C23=”north”)*(E15:E23=1)*

Create the sum array to total the values of the Sales column:

+SUMPRODUCT((B15:B23=”john”)*(C15:C23=”north”)*(E15:E23=1)*D15:D23)

Once your formula is complete, you can see that it magically calculated the sum of the matching values!

Round Off Numbers with Excel’s ROUND Formula

What does it do?
Rounds a number to the nearest decimal based on your specified number of digits
Formula breakdown:
=ROUND(number, num_digits)
What it means:
=ROUND(the number, number of decimal places to round off)

---

Ever had the need to round off numbers?
I do it all the time in my financial calculations.  For example, if I need to calculate percentage discounts and it gives me a number such as $47.4189349, rounding it off to $47.40 (round off to 1 decimal place) makes it so much more presentable!
In Excel, it’s very easy to do that with the ROUND formula!
I explain how you can do this below:

STEP 1: We need to enter the ROUND function:

=ROUND(

STEP 2: The ROUND arguments:

number

What is the number we want to round off?
Reference the cell that contains the number:

=ROUND(B9,

num_digits

Round off to how many digits?
Reference the cell that contains the number of digits:

=ROUND(B9, C9)

STEP 3: Do the same for the rest of the cells by dragging the ROUND formula all the way down using the left mouse button.
Now you are able to round off with different number of places!

Excel Subtotal Function – Avoid Double Counting

What does it do?
It returns a Subtotal in a list or database
Formula breakdown:
=SUBTOTAL(function_num, ref1)
What it means:
=SUBTOTAL(function number 1-11 includes manually-hidden rows & 101-111 excludes them, your list or range of data)
***Go to the bottom of this post to see what each value stands for

---

The SUBTOTAL function in Excel has many great features, like the ability to:
* Return a SUM, AVERAGE, COUNT, COUNTA, MAX or MIN from your data;
* Include hidden values within your data by entering the first argument function_num, as values between 1-11;
* Ignore hidden values within your data by entering the first argument function_num, as values between 101-111;
* Find the SUBTOTAL of filtered values;
* Ignore other SUBTOTALS that are included in your range, avoiding any double counting!

AVOIDING DOUBLE COUNTING WITH THE SUBTOTAL FUNCTION…

This is probably the most useful feature within the SUBTOTAL function!
Let’s say you have various SUBTOTALS within your data, one SUBTOTAL to Sum the North Region and another SUBTOTAL to Sum the South Region.
You can include a third SUBTOTAL for your Grand Total which references all of your data and ignoring the North & South Region SUBTOTALS, meaning that there is no double counting in your Grand Total.
See the below images of how this works with the SUBTOTAL function and how it double counts when using the SUM function:

***Values for the SUBTOTAL function_num:

Includes hidden values	Ignores hidden values	Function
1	101	AVERAGE
2	102	COUNT
3	103	COUNTA
4	104	MAX
5	105	MIN
6	106	PRODUCT
7	107	STDEV
8	108	STDEVP
9	109	SUM
10	110	VAR
11	111	VARP