Excel 2007 : vlookup and/or If Statement Help

[twak21]

This is driving me batty. I know I need 2 different vlookup and/or IF statements, but I can't figure it out. Here is my data.

Sheet 1 -
Company --- Line - -- Dollars

DC --- 300 --- $100
DC --- 300.1 --- $3,000
DC --- 400.1 --- $4,500
DC --- 420 --- $1,000
IL --- 300.1 --- $600
IL --- 302 --- $150
IL --- 400.1 --- $84
IL --- 420 --- $5,000

Sheet 2 -
Company --- Line 1 --- Line 2

DC --- ?? ----- ??
IL --- ?? ---- ??



On Sheet 2, I want Line 1 to = the sum of all 300 lines (meaning lines starting w/ a '3')from Sheet 1 for each company.

On Sheet 2, I want Line 2 to = line 420 from Sheet 1 for each company.

Thank you!

[zbor]

Try use a pivot table.

If you don't know how post a example workbook.

[NBVC]

For Line 1, try:

=SUMPRODUCT(--(Sheet1!$A$2:$A$9=$A2),--(LEFT(Sheet1!$B$2:$B$9)="3"),Sheet1!$C$2:$C$9)

where Shee1, A2:C9 contains your table and A2 of your summary sheet contains "DC"

copied down

for Line 2, formula:

=SUMPRODUCT(--(Sheet1!$A$2:$A$9=$A2),--(Sheet1!$B$2:$B$9=420),Sheet1!$C$2:$C$9)

[twak21]

NBVC - Awesome! This works great. Thanks so much - I would have never figured it out.