CODEWITHSUNDEEP
excelexcel-formula
Mark Mcnary
Mark Mcnary

Reputation: 3

find coordinates of a job number in one table and then find the numbers in matching coordinates in another table and sum those all up

i have 2 different equally sized tables in excel. one table has job numbers, the other table has hours worked, i want to search how many hours in total were spent on each job

for example

------hours---------------job #
m t w th f s s     --- | ---   m t w th f s s
8 8 6 8  8 0 0     --- | ---   1 1 2 5  4 0 0
8 8 8 8  7 0 0     --- | ---   2 2 5 4  1 0 0

What would a formula look like to find the sum of hours spent working on job #5? I import this data from google forms and I don't think I can change the format too much.

thanks

Upvotes: 0

Views: 39

Answers (3)

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

You can use SUMPRODUCT too:

enter image description here

=SUMPRODUCT(--($K$2:$Q$3=$I$7);$A$2:$G$3)

Upvotes: 0

Jerry
Jerry

Reputation: 71538

You could try a simple SUMIF:

enter image description here

=SUMIF(I3:O4,B6,A3:G4)

Changing the value of B6 to 5 will yield 16 hours in B7.

Upvotes: 2

Tim Williams
Tim Williams

Reputation: 166156

Here's a simplified example using a smaller set of data:

enter image description here

Enter formula in C10 and fill down

Upvotes: 1

Related Questions