# Discussion on: Harness the power of Subtotal in Excel to count grouped items

4

### Join the conversation!

View:
Show:
1 Vote
###### SUMIF
Shadeburst Updated - 22nd Jun 2011
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)
1 Vote
###### another twist
jbenton@... 28th Jun 2011
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)
1 Vote
###### array values
jbenton@... 28th Jun 2011
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
###### Using the Subtotal Function - Short Video on How it Works
danny@... 28th Jun 2011
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:

Danny Rocks
The Company Rocks
Keyboard Shortcuts:
Prev
Next
Toggle
###### Join the conversation
Formatting +
BB Codes - Note: HTML is not supported in forums
• [b] Bold [/b]
• [i] Italic [/i]
• [u] Underline [/u]
• [s] Strikethrough [/s]
• [q] "Quote" [/q]
• [ol][*] 1. Ordered List [/ol]
• [ul][*] · Unordered List [/ul]
• [pre] Preformat [/pre]
• [quote] "Blockquote" [/quote]

Join the TechRepublic Community and join the conversation! Signing-up is free and quick, Do it now, we want to hear your opinion.