Use info of another table

[Roeltje]

Hi,

I have a question.
Currently, i have 3 tables:

[name] [function]
Johan Cleaner
Bert Security
Gert Security

[name] [made hours] (hours)
Johan 3
Gert 2
Bert 3

[function] [total hours]
Cleaner 3
Security 5

De total hours have to be calculated by a function. How can i do this?

[tom1977]

Use

sumif

formula

regards

[NBVC]

If you can sort the first table by first column, alphabetically, then you can try this.

Assuming Table 1 (sorted by 1st column, ascending) is in A1:B4 (first row headers)

and table 2 is in E1:F4 (first row headers)

and table 3 is in H1:I3 (first row headers)

then if H2 contains Cleaner, in I2 enter:

=SUMPRODUCT(--(LOOKUP($E$2:$E$4,$A$2:$B$4)=H2),$F$2:$F$4)

copied down.

[Roeltje]

Well, these tables are in 3 different workbooks.
And i can't get the function names on alphabetic order (of course i know how, but ít's an export of another application, ordered by date).

[NBVC]

Ok, so let's now assume in Sheet1 of each workbook are the tables (and they are currently all open) and they have headers in Row 1... so data starts in Row 2 of each sheet.

In Boo1.xls is the first table, starting at A1:B4

in Book2.xls is the second table, starting at A1:B4

in Book3.xls is third table (summary), starting A1:B3

in B2 of third table use formula:

=SUMPRODUCT(--(ISNUMBER(MATCH([Book2.xls]Sheet1!$A$2:$A$4&A2,[Book1.xls]Sheet1!$A$2:$A$4&[Book1.xls]Sheet1!$B$2:$B$4,0))),[Book2.xls]Sheet1!$B$2:$B$4)

copied down.

You can close the first 2 workbooks after this.

[NBVC]

Using the sample you last posted formula would be:

=SUMPRODUCT(--(ISNUMBER(MATCH(Uren!$A$1:$A$3&A1,Functies!$A$1:$A$3&Functies!$B$1:$B$3,0))),Uren!$B$1:$B$3)

copied down.

[Roeltje]

See attachtment for example.

[Roeltje]

Worked! Thanks~!

[NBVC]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save