Match, Index, Vlookup..any or none?

[slingshot]

Hi,
I have 2 sets of data (simplified) as such:


DATA-SET 1

	A	B	C	D
1	INITIATIVE	WEST	EAST	CENTRAL
2	PROJECT1	1	1
3	PROJECT2		1	1

<TBODY>
</TBODY>

In all there are over 500 rows in Column A (initiatives) and 8 different regions (represented by Column B, C, D)

The 1's represent that a specific Region is impacted by whatever initative in Column A.


DATA-SET 2

	A	B	C	D
6	INITIATIVE	TIME SPENT	ACTION	WEEK START
7	PROJECT1	2	C	2014/09/22
8	PROJECT1	2.5	C	2014/09/29
9	PROJECT2	1.5	F	2014/09/22
10	PROJECT2	2	C	2014/09/22
11	PROJECT2	.25	C	2014/09/29

<TBODY>
</TBODY>

This set of data represents how much time will be spent on each Initative from Data-set 1 by week. (Column B) The weeks continue past the above example at least into 2016.


I am trying to summarise the hours spent by week by region. With the simplified data the results would look like this:

	F	G	H
2	REGION	2014/09/22	2014/09/29
3	WEST	2	2.5
4	EAST	5.5	2.75
5	CENTRAL	3.5	.25

<TBODY>
</TBODY>

My main issue is that the various combinations of 8 regions gives me 255 various combinations, so IF, AND or OR would involve a lot of keying (at least where my skill set would take me). I am confused by arrays, but maybe they are the answer.

Any suggestions, comments or outright solutions would be greatly appreciated.

 

[Marcelo Branco]

Welcome to Mr Excel

Maybe something like...

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

1​

INITIATIVE​

WEST​

EAST​

CENTRAL​

INITIATIVE​

TIME SPENT​

ACTION​

WEEK START​

REGION​

22/09/2014​

29/09/2014​

2​

PROJECT1​

1​

1​

PROJECT1​

2​

C​

22/09/2014​

WEST​

2​

2,5​

3​

PROJECT2​

1​

1​

PROJECT1​

2,5​

C​

29/09/2014​

EAST​

5,5​

2,75​

4​

PROJECT2​

1,5​

F​

22/09/2014​

CENTRAL​

3,5​

0,25​

5​

PROJECT2​

2​

C​

22/09/2014​

6​

PROJECT2​

0,25​

C​

29/09/2014​

<tbody>
</tbody>

Array formula in L2
=SUM(SUMIFS($G$2:$G$6,$F$2:$F$6,IF(($B$1:$D$1=$K2)*($B$2:$D$3=1),$A$2:$A$3),$I$2:$I$6,L$1))

confirmed with Ctrl+Shift+Enter, not just Enter

copy across and down

Hope this helps

M.

 

[slingshot]

Arrays. I must learn them.

This worked great on my test data. Thank you very much, your assistance is greatly appreciated.

 

[Marcelo Branco]

Arrays. I must learn them.

This worked great on my test data. Thank you very much, your assistance is greatly appreciated.

You are welcome. Glad for helping

M.