I have a database that consists of the following:
Col A: case number (e.g. 1993-01-115, 2002-11-078, etc.)
Col B: username (e.g. John Henry, Jane Doe, etc.)
Col C: hours (number of hours each user worked on case number)
Col D: case owner name

Col A contains repeats of some case numbers and is sorted ascending
Col B contains repeats of some usernames

I need to add the hours in Col C depending on:

Each time a unique case number is encountered, check Col D (case owner name)
and lookup this name in Col B (username) and sum the hours for each instance
the case owner appears.
Database example:
Case No Username Hours Owner
1993-01-115 Jane Doe 2 Jane Doe
2004-05-020 John Henry 1 Theresa Chan
2004-05-020 Theresa Chan 2 Theresa Chan
2004-05-020 Theresa Chan 3 Theresa Chan
2004-05-020 Larry Roberts 1 Theresa Chan
2004-11-072 Andrew Dunn 1 Andrew Dunn

So using the above example, case 1993-01-115 owner is Jane Doe and total
hours is 2, case 2004-05-020 the owner is Theresa Chan and total hours is 5,
case 2004-11-072 the owner is Andrew Dunn and total hours is 1 and so on.

Could someone help me write a formula to accomplish this? Thanks!

Hi,

If I understand correctly then with the case number you are looking for in
E1 try this

=SUMPRODUCT((A1:A10=E1)*(B1:B10=D1:D10)*(C1:C10))

Mike

"tjd59" wrote:

I have a database that consists of the following:
Col A: case number (e.g. 1993-01-115, 2002-11-078, etc.)
Col B: username (e.g. John Henry, Jane Doe, etc.)
Col C: hours (number of hours each user worked on case number)
Col D: case owner name

Col A contains repeats of some case numbers and is sorted ascending
Col B contains repeats of some usernames

I need to add the hours in Col C depending on:

Each time a unique case number is encountered, check Col D (case owner name)
and lookup this name in Col B (username) and sum the hours for each instance
the case owner appears.
Database example:
Case No Username Hours Owner
1993-01-115 Jane Doe 2 Jane Doe
2004-05-020 John Henry 1 Theresa Chan
2004-05-020 Theresa Chan 2 Theresa Chan
2004-05-020 Theresa Chan 3 Theresa Chan
2004-05-020 Larry Roberts 1 Theresa Chan
2004-11-072 Andrew Dunn 1 Andrew Dunn

So using the above example, case 1993-01-115 owner is Jane Doe and total
hours is 2, case 2004-05-020 the owner is Theresa Chan and total hours is 5,
case 2004-11-072 the owner is Andrew Dunn and total hours is 1 and so on.

Could someone help me write a formula to accomplish this? Thanks!

Thank you for the quick response! However, I'm having trouble with the
formula - it either returns #VALUE! or if I turn off error checking for
"formula omits cells in region" I get 0's.

I copied and pasted your formula into my sample spreadsheet as described
below.

P.S. once the formula works, can I copy it to all other cells?
As you might have guessed, I'm a bit of a newbie with Excel formulas!


"Mike H" wrote:

Hi,

If I understand correctly then with the case number you are looking for in
E1 try this

=SUMPRODUCT((A1:A10=E1)*(B1:B10=D1:D10)*(C1:C10))

Mike

"tjd59" wrote:

I have a database that consists of the following:
Col A: case number (e.g. 1993-01-115, 2002-11-078, etc.)
Col B: username (e.g. John Henry, Jane Doe, etc.)
Col C: hours (number of hours each user worked on case number)
Col D: case owner name

Col A contains repeats of some case numbers and is sorted ascending
Col B contains repeats of some usernames

I need to add the hours in Col C depending on:

Each time a unique case number is encountered, check Col D (case owner name)
and lookup this name in Col B (username) and sum the hours for each instance
the case owner appears.
Database example:
Case No Username Hours Owner
1993-01-115 Jane Doe 2 Jane Doe
2004-05-020 John Henry 1 Theresa Chan
2004-05-020 Theresa Chan 2 Theresa Chan
2004-05-020 Theresa Chan 3 Theresa Chan
2004-05-020 Larry Roberts 1 Theresa Chan
2004-11-072 Andrew Dunn 1 Andrew Dunn

So using the above example, case 1993-01-115 owner is Jane Doe and total
hours is 2, case 2004-05-020 the owner is Theresa Chan and total hours is 5,
case 2004-11-072 the owner is Andrew Dunn and total hours is 1 and so on.

Could someone help me write a formula to accomplish this? Thanks!