Searching and Sum with VLOOKUP, AND, IF

[solo9999]

Hi all,

I am working on a table which basically has three columns with list menus in their cells: Area (3 choices list menu), Type (two choices list menu) and Hours (12 choices list menu, 1-12). When filled out, it looks something like this:

AREA-TYPE-HOURS
ABC-AAA-5
DEF-BBB-3
ABC-AAA-4
GHI-AAA-8
DEF-AAA-5

So what I want to accomplish in the end is to do the following: Sum all AAA type of hours for all ABC Areas; Sum all BBB type of hours for all ABC areas... and so on. Basically an overview for each area wit the two types of hours summed up.

Is is going to be something with an IF(AND( statement within a VLOOKUP?

!Thanks!

 

[p45cal]

3 solutions:
The formulae in B11 and B16 can be copied across and down.
The pivot table (F1:J5) is the fastest way.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	AREA	TYPE	HOURS			Sum of HOURS	Column Labels
2	ABC	AAA	5			Row Labels	ABC	DEF	GHI	Grand Total
3	DEF	BBB	3			AAA	9	5	8	22
4	ABC	AAA	4			BBB		3		3
5	GHI	AAA	8			Grand Total	9	8	8	25
6	DEF	AAA	5
7
8
9	Summary Excel 2007 on:
10		ABC	DEF	GHI
11	AAA	9	5	8
12	BBB	0	3	0
13
14	Summary pre-Excel 2007:
15		ABC	DEF	GHI
16	AAA	9	5	8
17	BBB	0	3	0
Sheet6

 

[solo9999]

Dear p45cal,

Thank you very much for this, it was exactly what I was looking for and much easier than I was initially trying to make it.

All best to you!!!

 

[solo9999]

Hi again,

Kind of an issue here with this. I used Excel 2007 but apparently the SUMIF is not available in 2003 which unfortunately I am still using @ work. What would be the 2003 version of this formula?

!Thanks!

 

[solo9999]

Hi again,

Kind of an issue here with this. I used Excel 2007 but apparently the SUMIF is not available in 2003 which unfortunately I am still using @ work. What would be the 2003 version of this formula?

!Thanks!

Sorry I completely ignored the bottom part of your post. The solution is already there once again than you very very much!