Thread: Trouble with matching multiple criteria then summing one column

Hello!

I am working on a project analyzing personal financial data. However, I need to look at it on a yearly basis, meaning I must sum totals per individual-per year. Here is a sample of my spreadsheet:

Year Last First Birth Date $ Amt Paid

2006 MICH MARY 01/03/1955 $115.00
2006 MICH MARY 01/03/1955 $162.00
2007 MICH MARY 01/03/1955 $1,051.64
2007 MICH MARY 01/03/1955 $6,861.28
2008 MICH MARY 01/03/1955 $682.40
2008 MICH MARY 01/03/1955 $-
2008 MICH MARY 01/03/1955 $13,244.07
2008 MICH MARY 01/03/1955 $10,362.41
2009 MICH MARY 01/03/1955 $(61.00)
2009 MICH MARY 01/03/1955 $6,254.99
2009 MICH MARY 01/03/1955 $4,096.08
2009 MARY MARY 01/03/1955 $2,238.54
2007 PAIG MAKE 01/05/1976 $-
2007 PAIG MAKE 01/05/1976 $555.54
2008 PAIG MAKE 01/05/1976 $246.00

This is basically a short snippet of my spreadsheet (names/d.o.b.'s have been changed). Basically, I need to match the year, last, and birth date, and sum their respective $ Amt Paid's, and come up with a single total per year/per person. Is there any way to do this without using a pivot table? I would like to just apply the formula to all of my spreadsheets as I have multiple sheets with over 10,000 rows of information.

If you have a solution, it will save me hours upon hours of work. Any tips are appreciated!

In each of the sheets in the same cells off to the right somewhere you would need to enter the unique criteria...

then you can use SUMIFS() to sum the respective totals.

e.g. =SUMIFS(D:D,A:A,X1,B:B,Y1,C:C,Z1)

where A:D contains your table and X1, Y1, Z1, the variables to match up in columns A, B and C, respectively to Sum up column D.

Then in summary sheet, you need something like

=SUM('Sheet1:Sheet10'!AA1) copied down...

where AA1 on each of the sheets Between Sheet1 and Sheet10 contains the sum of a specific combination of criteria.