IF statement with multiple criteria - I think.

[14peace]

Column A: is a Name

Column B: contains areas associated with multiple projects. AreaA, AreaB, AreaC etc.

Column C: is a Start Time

Column D: is a Finish Time

Column E: is Project 1

Column F: Project 2

Column G: Project 3

I need to be able to look to Column B: and have the areas that relate to (each project has a different number of Areas associated with it) Projects 1,2,3 list the elapsed time (Column D - Column C)under the corresponding Project.

For example: Areas A,B,C,F,Y,Z belong to Project 1. Areas D,G,H,I,J,K,L,M,N to Project 2 and Areas O,P,Q,R,S to Project 3.

So, if cell B2 = "AreaF" it needs to have D2-C2 difference placed in E2. If cell B3="AreaP" it needs to have D3-C3 difference placed in G3, etc.

There may be up to 25 Areas per Project and there may also be more than 3 Projects at some times.

I'll hope this is a clear enough description and hope one of you experts can lead me to a formula that will make the needed calculations. I was hoping I could nest IF statements in some way but have been unsuccessful. Your help is much appreciated.

[Juan Pablo González]

Ok, let's do this step by step to see if we can work it out.

First, do you have a table where you have all your areas and which project are they attached to ? like this:

Area - Project
A - 1
B - 2

etc.

[14peace]

Yes, the table sets up as follows:

A=Name B=Area C=TimeOut D=TimeIn F=Project1
Joe AreaJ 12:00 13:25
Sam AreaX 12:00 15:12
Sue AreaN 12:00 14:55
Allen AreaB 12:00 16:00
etc. etc. etc. etc.


G=Project2 H=Project3

Where Areas J,X,D,K etc. belong to Project1 and Areas N,B,S,F,T,L etc. belong to Project2 and so forth...does that help?

[14peace]

Hello, Juan Pablo G.

I may have misunderstood your question. If you are asking if I have a separate table with only areas and projects the answer is negative. I only have the one sheet set up as in the prior post.

Your consideration of this problem is much appreciated. Thanks.

[Aladin Akyurek]

On 2002-05-23 07:01, 14peace wrote:
Hello, Juan Pablo G.

I may have misunderstood your question. If you are asking if I have a separate table with only areas and projects the answer is negative. I only have the one sheet set up as in the prior post.

Your consideration of this problem is much appreciated. Thanks.

I believe you need a table. Your data is large: an array constant instead of a table will be a burden. Why not create the required table in a separate worksheey (calle, say, Admin) which you can protect and hide.

[14peace]

Making a table is no problem if that is required for the solution...consider it done.

With the table now in place on a second sheet how will I get the data to calculate on sheet one?

sheet 2 is table:
Areas - Projects
xxxx - project 1
xxxy - project 1
xxxz - project 1
xzxz - project 2
xZxx - project 2

and so on. So how will I use this to calculate what I need on sheet 1?

TIA,

[Aladin Akyurek]

On 2002-05-23 12:43, 14peace wrote:
Making a table is no problem if that is required for the solution...consider it done.

With the table now in place on a second sheet how will I get the data to calculate on sheet one?

sheet 2 is table:
Areas - Projects
xxxx - project 1
xxxy - project 1
xxxz - project 1
xzxz - project 2
xZxx - project 2

and so on. So how will I use this to calculate what I need on sheet 1?

TIA,

Select all of the cells of the table in Sheet2 and name it, say, AreaTable, via the Name Box on the Formula Bar.

I assume you have projects in E1 (Project 1), F1, G1, etc.

In E2 enter and copy across:

=(E$1=VLOOKUP($A2,AreaTable,2,0))*($D2-$C2)

Caveat. The above formula requires that there is no area code in A that is not available in AreaTable.