Conditional sum with linked conditions in different tables

[ExcelStefan]

Hi!

I have two tables: one giving the link between a person and his job

Chelsea Architect
John Driver

and a second one, which says, who works in what week

John 1 1
Chelsea 1

I'd like to know, let's say, how many architects work in what week. In other words, I'd like to sum all the values of people, who are architects.

Does somebody have an idea, how to "make Excel know" that Chelsea is an architect?

Thanks!!

[Marcol]

What's the point in having two tables?
Can one person have more than one job?

The easiest way is to add a helper column to Sheet "Input2", say Column B, this will avoid nasty array formulae.
Then in B2

Please Login or Register  to view this content.

Drag/Fill Down

And in Sheet "Result" B2

Please Login or Register  to view this content.

Drag Across to Column D, then down.

[ExcelStefan]

Hi Marcol

Thanks for your reply! The point of having several tables is for maintenance: I would like to pass this Excel sheet to people who don't know Excel. They should be able to copy and paste their tables (which have a given configuration) without having to think too much.

A person can have only one job, but a person can have several "presence lines".

Here's a sheet which contains vectors I wish (without the formulas to obtain them).

Thanks!

[ExcelStefan]

What is needed is a vector (array), which checks a condition for every element of the vector. With

Jack 1 1 1
John 1 1
Chelsea 1
Nina 1
Jim 1 1
Jim 1 1 1

And

Chelsea Architect
John Driver
Jack Driver
Nina Salesperson
Jim Salesperson

We need the following vector (which checks e.g. the job "Salesperson" at every name in the first table):

0
0
0
1
1
1

Does somebody know how to make a formula which returns a vector (array) with a condition? I imagine a function like =IF returning an array.

[ExcelStefan]

I have found a partial solution. I get the array

Jack Driver
John Driver
Chelsea Architect
Nina Salesperson
Jim Salesperson
Jim Salesperson

Now, I would like to avoid writing down this array, but rather include the corresponding [code] =VLOOKUP($I$20:$I$25;$I$7:$J$11;2;0) [code] in the final [code] =SUMMENPRODUKT(($G$8:$G$13=$A3)*($J$19:$L$19=B$1);$J$20:$L$25) [code] Until now, it doesn't work.

[Marcol]

Seems that the only problem with the solution I offered in post #2, was where I chose to put the helper column.

See if this suits you better
You can hide the helper (Sheet "Result" "Convert Input2!A:A") with the grouping button if you want to.