RE: How to sum cells based on conditions

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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!



.

Relevant Pages

  • How to sum cells based on conditions
    ... username ... Each time a unique case number is encountered, check Col D (case owner name) ... 2004-05-020 Theresa Chan 2 Theresa Chan ... case 1993-01-115 owner is Jane Doe and total ...
    (microsoft.public.excel.worksheet.functions)
  • RE: How to sum cells based on conditions
    ... username ... Each time a unique case number is encountered, check Col D (case owner name) ... 2004-05-020 Theresa Chan 2 Theresa Chan ... 2004-11-072 Andrew Dunn 1 Andrew Dunn ...
    (microsoft.public.excel.worksheet.functions)
  • Re: How do I eliminate the need for Log On information?
    ... With Owner and password left ... I was not required to enter a username and password; ... how do I eliminate this window showing ... I would like to simply power on, ...
    (microsoft.public.windowsxp.general)
  • Re: Change name Owner in XP Home Edition
    ... can anyone tell me how to change the user name 'Owner' in XP Home ... Right click on the username, select properties, change the username. ... A better way would be to create a new user with a proper username and copy ... the old user profile to the new users profile directory with FAST or better ...
    (microsoft.public.windowsxp.security_admin)
  • Re: XP pro password help..
    ... just re-format the whole thing. ... With the chached accounts for the you may not ... > have the right username and password combo, ... I did call the previous owner and she ...
    (microsoft.public.win2000.security)