Subtotal with Variable Row

It is common to have subtotal on top of each table. 

Most people use subtotal to get total amount of visible row when filtered. That is SUBTOTAL(9,.......). 

What if... There are consistent changes in data in the table resulted in changes to number of row (reduce or increase).  

Most people will change the row number in subtotal formula manually. However, this is error-pro as human being, we always forgot! 

Here is the magic formula to address the issue. The formula will cover until the last row when there are changes in subtotal range. Valid until column Z only.

SUBTOTAL(9,INDIRECT(CHAR(COLUMN()+64)&ROW()+2):INDIRECT(CHAR(COLUMN()+64)&COUNTA($A:$A)+2)) 

Where:

INDIRECT function returns a reference to a range. 

CHAR(65) is A. 

CHAR(66) is B. and so on.

COLUMN() will return current column number. 

Example: 

COLUMN(A1) = 1 

COLUMN(B2) = 2 

CHAR(COLUMN(A1)+64)=CHAR(1+64)=CHAR(65)=A 

ROW() will return current row number. 

ROW()+2 is my first row to sum. Why +2? Because my current row contains the subtotal formula. +1 is header/ title. My range starts from +2. +2 is variable depending on the gaps from cell row 1 to the header. 

COUNTA is to count non-blank.

COUNTA($A:$A) is counting non-blank range in column A. I prefer column A because it is always the beginning of my data.

Subtotal with Sumproduct

Formula: 

SUMPRODUCT($A2:$A10,SUBTOTAL(9,OFFSET(B2:B10,ROW(B2:B10)-MIN(ROW(B2:B10)),0,1)))

SUMPRODUCT

The SUMPRODUCT function returns the sum of the products of corresponding ranges or arrays. The default operation is multiplication.

The array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value. For example, =SUMPRODUCT(C2:C10,D2:D5) will return an error since the ranges aren't the same size.

SUMPRODUCT treats non-numeric array entries as if they were zeros.

Basic function like below where range C2:D5 contained numbers.

SUMPRODUCT(C2:C5,D2:D5)

The longer formula is C2*D2+C3*D3+C4*D4+C5*D5 will return the same result.

There is a little bit advance function when there are specific conditions required. For example, in below case, there are repetitive characters in range B2:B10 and C2:C10. There are conditions to sum the products of B2:B10 and C2:C10 that are located in cell B15 and C15 respectively.

SUMPRODUCT((B2:B10=B15)*(C2:C10=C15)*D2:D10)

More advance function to find SUMPRODUCT of visible cell only per below. In this function, hidden rows will be considered as 0.

SUMPRODUCT(B2:B10,SUBTOTAL(9,OFFSET(C2:C10,ROW(C2:C10)-MIN(ROW(C2:C10)),0,1)))

Subtotal

For analytics, it is highly recommended to always have subtotal at the top of any table that contains numbers. 

Syntax: SUBTOTAL(function_num,ref1,[ref2],...)

Where:

function_num = The number 1-11 or 101-111 that specifies the function to use for the subtotal

ref1 = The first named range or reference for which you want the subtotal

Ref2 = Optional. Additional range if any

There are 2 types of subtotal:

Type 1: Value from 1-11. It includes hidden values in subtotal range

Type 2: Value from 101-111. It ignores hidden value in subtotal range

Left, Right, Mid

LEFT(text_string, char_numbers)
Returns the specificed number of characters from start of a string

RIGHT(text_string, char_numbers)
Returns specified number of characters from the end of string

MID(text_string, start_number, char_numbers)
Returns the characters from the middle of string by giving starting position and length of characters to return

Managing Errors

Below are common errors in excel and their definitions:

#N/A - generally indicates that a formula can’t find what it’s been asked to look for.
#VALUE! - something wrong with the formula OR the cells you are referencing.
#REF! - shows when a formula refers to a cell that’s not valid.
#DIV/0! - when a number is divided by zero (0).
#NUM! - a formula or function contains numeric values that aren’t valid.
#NAME? -  there is a typo in the formula name. Example COUNTIF not COUNTIIIF.
#NULL! - specify an intersection of two areas that do not intersect.


IFERROR

Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula.

Syntax: IFERROR(value, value_if_error)

Example: IFERROR(2/X,"Error")

if X = 1, RESULT = 2
if X = 0, RESULT = Error

OFFSET Function

Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells.

OFFSET(reference, rows, cols, [height], [width])

Height and Width are optional.

From a reference, system will determine the location by:

1. Moving down (+ve value) or up (-ve value) based on ROWS
2. Moving right (+ve value) or left (-ve value) based on COLS

Example:
Reference = A1
ROWS = 3
COLS = 2

Move 3 cells below cell A1 then 2 cells right after cell A1
RESULT = C4

OFFSET: Click to enlarge picture

Index Function

INDEX returns a value at a given position in a range or array.

INDEX (array, row_num, [col_num])
returns the value in the cell at the intersection of row_num and column_num.

INDEX(reference, row_num, [column_num], [area_num])
returns the reference of the cell at the intersection row_num and column_num.

INDEX: Click to enlarge picture