How to sum cells based on conditions
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! |
How to sum cells based on conditions
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! |
How to sum cells based on conditions
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! |
All times are GMT +1. The time now is 03:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com