Looking up data using multiple criteria

[joseway1979]

Hi,

I need to be able to get an amount spent per month on certain tasks.

My spreadsheet has costs for cleaning, repairs, etc corresponding to each month, see below;

Jan Feb Mar
Cleaning 10 10 10
Repairs 10 10 10

What I want to do is get a total cost per month for each task, but by typing the month into the cell, so, I would want to type Feb into A1 and I would imagine I would need to type Repairs into A2 and that would give me 10 in A3.

Hope that's clear.

Any help is much appreciated.

 

[Weazel]

maybe something like...

a3=SUMIF(C2:C3,A2,INDEX(D2:F3,,MATCH(A1,D1:F1,0)))


[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]mar[/TD]
[TD][/TD]
[TD]Task[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]repairs[/TD]
[TD][/TD]
[TD]Cleaning[/TD]
[TD]

20​

[/TD]
[TD]

35​

[/TD]
[TD]

45​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

30​

[/TD]
[TD][/TD]
[TD]Repairs[/TD]
[TD]

10​

[/TD]
[TD]

20​

[/TD]
[TD]

30​

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Airfix9]

I put the data in cells B1:E3, the month to match in A1, the cost type in A2 and in A3 used this formula:

=HLOOKUP(A1,B1:E3,MATCH(A2,B2:B3)+1,0)