How to use index match for multi lookups?
 

I have two worksheets I am working on. On one worksheet I have the same
project number listed multiple times with $ amounts listed by months. On the
other worksheet I need to sum the $ amounts by project number and by month.
How can I do this with reliable accuracy?

Data example:
Project # Month Amt $
4004 Jan-07 50
4004 Jan-07 25
5005 Feb-07 35
5005 Feb-07 40
5005 Jun-07 50
6006 Apr-07 75

How to use index match for multi lookups?
 

Are the dates real dates or just text?

This will sum all project 4004 for Jan if it is text
=SUMPRODUCT(--(A1:A100=4004), --(Left(B1:B100,3)="Jan),C1:C100)

For more details on SUMPRODUCT
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html

Another solution is a Pivot table
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
http://www.techonthenet.com/excel/pivottbls/index.htm
http://www.dicks-blog.com/archives/2...le-parameters/

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"rcnclovis" wrote in message
...
I have two worksheets I am working on. On one worksheet I have the same
project number listed multiple times with $ amounts listed by months. On
the
other worksheet I need to sum the $ amounts by project number and by
month.
How can I do this with reliable accuracy?

Data example:
Project # Month Amt $
4004 Jan-07 50
4004 Jan-07 25
5005 Feb-07 35
5005 Feb-07 40
5005 Jun-07 50
6006 Apr-07 75