Discussion on:

Harness the power of Subtotal in Excel to count grouped items

My favorite is SUMIF to summarize a list of transactions by transaction type. Say for example col A is Date, B is Type, C is Reference, D is Amount. Types = BAL, INV, CRN, PAY, JRN. Below the data create a vertical list of the Types in col B and in cell D110 insert this formula (data runs from Row 2 to Row 100, summary starts on Row 110):

=SUMIF(B$2:B$100,B110,D$2:D$100)

and copy it downward.

The result will look something like

BAL 999,999.99
INV 99,999.99
CRN 99,999.99
JRN 99,999.99
PAY 99,999.99

(Apologies for layout, this text box doesn't seem to support tabbing)

if you're writing the formulae longhand, SUBTOTAL() can be made to ignore or include hidden values;
just add 100 to the aggregate function
so if you want to count just visible numeric cells, use SUBTOTAL(103,ranges) [unless you've used autofilter, in which case values hidden by this will always be ignored]

it's also worth noting that SUBTOTAL() ignores any nested subtotals in its specified ranges (so no double counting)

and finally, if you're having trouble remembering the order of aggregate fucntions, they're alphabetical (took me ages to spot that one! - only when i wrote out a crib list that i noticed it)

SUBTOTAL() will always evaluate as an array function
so if one of your range references is like A1:A99="Y" then there's no need to press CTRL-SHIFT-ENTER

btw you might want to use this array funtionality to create a COUNTIF/SUMIF type function for multiple conditions
eg SUBTOTAL(9,range1=condition1,range2=condtition2,range3) will sum only the values in range3 that meet the specified criteria in range1 and range2

I have posted a short video where I demonstrate how to use the SUBTOTAL Function in a column that is apart from the columnar data. Here is the link:

http://www.thecompanyrocks.com/dont-subtotal-excel-data-use-subtotal-function-instead/

Danny Rocks
The Company Rocks