Vlookup/Hlookup need help

[kredstonepop]

Hello,

I have GL data that basically looks like this

A1:GL Acct # B1: Name C1: Total D1: Department 1 E1: Department 2 F1: Department 3
A2: 65000 B2: Wages C2: 1,000,000 D2: 500,000 E2: 250,000 F2: 250000
A3: 79000 B3: Travel C3: 250,000 D3: 100,000 E3: 100,000 F3: 50,000

Question: I want to do a lookup as follows:

A1: Account# B1: Department C1: Amount
A2: 65000 B2: 3 C3: ??????????

So basically I want to know what department 3's cost was in account 65000.

My data isn't all that large, I go from A1:A400.

Thank you in advance!!!

 

[jasonb75]

Try

=SUMIFS(INDEX($A:$F,0,MATCH("Department 3",$1:$1,0)),$A:$A,65000)

The criteria can be directed to other cells as desired.

 

[petertenthije]

=sumifs($f:$f,$a:$a,65000)