Summing values in database according to multiple criteria

[Guest]

Dear All
I have a spreadsheet;
A B D E
Dept Account Staff Amount
1 AA 1002 Peter 10
2 BB 1002 John 20
3 AA 1002 Peter 30
4 CC 1002 Paul 40

I want to sum the content column E by Dept (A), Account Number (B), and Staff (D). How can I do this by formula? As opposed to pivot tables.
Many thanks
Peter

 

[Andy Wiggins]

Take a look at the SUMPRODUCT function.

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"

Dear All
I have a spreadsheet;
A B D E
Dept Account Staff Amount
1 AA 1002 Peter 10
2 BB 1002 John 20
3 AA 1002 Peter 30
4 CC 1002 Paul 40

I want to sum the content column E by Dept (A), Account Number (B), and

Staff (D). How can I do this by formula? As opposed to pivot tables.

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(--(A1:A100="AA"),--(B1:B100=1002),--(C1:C100="Peter"),D11
00)

or use a pivot table for this. See:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm