Gather and subtotal data

**Adamk12** · 03-16-2015, 11:07 PM

I'd like to know if there is a way to automate the process of subtotaling data in a spreadsheet? In the attached example, I'd like to subtotal the $ amount for each name, but not have to manually sort and subtotal. If I knew what names to expect, I could use the sumif function, but how can it be done if I don't know what names to expect?

**FDibbins** · 03-16-2015, 11:19 PM

Hi, welcome to the forum

You gave no indication of what you wanted the answer to look like, so try this summary table...

	A	B	C	D	E	F
2	Name	Address	Amount
3	Smith	101 Elm St.	$ 400.00		Smith	503.32
4	Jones	48 Cherry Tree	$ 32.50		Jones	1917.51
5	Smith	101 Elm St.	$ 68.88		Cline	1.23
6	Cline	390 1st St.	$ 1.23		Ash	309.31
7	Ash	21 Grand Ave.	$ 222.21		Wilson	111.55
8	Jones	48 Cherry Tree	$ 1.99		Washington	235.54
9	Smith	101 Elm St.	$ 34.44		Elliot	16.5
10	Ash	21 Grand Ave.	$ 87.10
11	Wilson	1414 3rd St.	$ 111.55
12	Jones	48 Cherry Tree	$ 291.68
13	Washington	68 Center St.	$ 235.54
14	Elliot	400 Grand Ave, Apt B	$ 16.50
15	Jones	48 Cherry Tree	$ 1,236.50
16	Jones	48 Cherry Tree	$ 354.84

E3=IFERROR(INDEX($A$3:$A$16,MATCH(0,INDEX(COUNTIF($E$2:E2,$A$3:$A$16),0,0),0)),"")
This will pull in unique names

F3=IF(E3="","",SUMIF($A$3:$A$16,E3,$C$3:$C$16))
to sum by name

**Adamk12** · 03-17-2015, 06:26 PM

Ford, thanks for the quick response; this is exactly what I was looking for. I understand the sumif function in cell F3, but am a little stumped on how E3 is generating the unique list of names. I can blindly apply this approach to my situation, but would appreciate if you could walk me through the nested functions. Thanks again.