Using conditional formulas to sum up data in Microsoft Excel
February 5, 2008 6:34 PM Subscribe
Can I use Excel to loop through lists of names and sum up numbers for each name, without having to define the cell ranges that correspond to each name?
(This is Excel 2003.)
I have an Excel workbook with many worksheets inside. Each worksheet corresponds to an area of responsibility. Each row has a person's name on it, but each person can have many rows. So when sorted by name, each worksheet looks like this:
Doe, John x x x 1.00 x
Doe, John x x x 4.00 x
Smith, Jane x x x 8.00 x
Smith, Jane x x x 6.00 x
Where each x represents a column of data I don't care much about. There are no blank lines between the names; there's just a header row at the top of the spreadsheet, then solid data until the end of the name list.
I also have a summary worksheet with one row for each worksheet. The summary worksheet uses =SUM(WorksheetName!StartingCell:EndingCell) to add up the numbers column.
What I really want to do is create another summary worksheet that would have a row for each worksheet + person combination that sums that person's total numbers on that worksheet. So it would look like:
Worksheet Name
Doe, John 5.00
Smith, Jane 14.00
Repeated for each worksheet and the people who have numbers on that worksheet.
I want a formula that will:
1. Look at the each cell in the first column of a given worksheet.
2. For each cell that contains a name, skip over four columns and add the number in that cell to a running total.
3. Continue until it hits a different name, then dump the total in a cell on my "person summary" worksheet.
3. Repeat that process for the next name in the worksheet, until it comes to the first blank cell in the first column.
Can Excel handle this type of conditional formula? I can't find an example that seems analogous to what I want -- or if I have, I haven't recognized it.
(This is Excel 2003.)
I have an Excel workbook with many worksheets inside. Each worksheet corresponds to an area of responsibility. Each row has a person's name on it, but each person can have many rows. So when sorted by name, each worksheet looks like this:
Doe, John x x x 1.00 x
Doe, John x x x 4.00 x
Smith, Jane x x x 8.00 x
Smith, Jane x x x 6.00 x
Where each x represents a column of data I don't care much about. There are no blank lines between the names; there's just a header row at the top of the spreadsheet, then solid data until the end of the name list.
I also have a summary worksheet with one row for each worksheet. The summary worksheet uses =SUM(WorksheetName!StartingCell:EndingCell) to add up the numbers column.
What I really want to do is create another summary worksheet that would have a row for each worksheet + person combination that sums that person's total numbers on that worksheet. So it would look like:
Worksheet Name
Doe, John 5.00
Smith, Jane 14.00
Repeated for each worksheet and the people who have numbers on that worksheet.
I want a formula that will:
1. Look at the each cell in the first column of a given worksheet.
2. For each cell that contains a name, skip over four columns and add the number in that cell to a running total.
3. Continue until it hits a different name, then dump the total in a cell on my "person summary" worksheet.
3. Repeat that process for the next name in the worksheet, until it comes to the first blank cell in the first column.
Can Excel handle this type of conditional formula? I can't find an example that seems analogous to what I want -- or if I have, I haven't recognized it.
Best answer: You don't need to loop... you can do this with the SUMIF() function.
So if your names are in column A, and your numbers in column E:
=SUMIF(WorksheetName!A:A,"John Doe",WorksheetName!E:E)
will give you the total for John Doe.
posted by pompomtom at 7:01 PM on February 5, 2008
So if your names are in column A, and your numbers in column E:
=SUMIF(WorksheetName!A:A,"John Doe",WorksheetName!E:E)
will give you the total for John Doe.
posted by pompomtom at 7:01 PM on February 5, 2008
Response by poster: Wow, that was about a million times simpler than I thought it was going to be. I wish I had realized SUMIF worked that way! Thanks, pompomtom!
PercussivePaul, thank you for reminding me about pivot tables; I don't need them this time around, but I think they may come in handy with some reports that I'm probably going to have to put together soon.
posted by korres at 7:12 PM on February 5, 2008
PercussivePaul, thank you for reminding me about pivot tables; I don't need them this time around, but I think they may come in handy with some reports that I'm probably going to have to put together soon.
posted by korres at 7:12 PM on February 5, 2008
One caveat about using SUMIF: You'll use the formula for a few months until you've forgotten you're using it at all. As soon as that happens, you'll add John Q. Public to your worksheets and forget to add him to your summary sheet. Be sure to use a sanity check with your worksheet grand totals to make sure you haven't missed any data.
posted by stefanie at 7:31 PM on February 5, 2008
posted by stefanie at 7:31 PM on February 5, 2008
« Older College-level "book reports" -- are they possible? | Please help me find the title to this sci-fi book Newer »
This thread is closed to new comments.
posted by PercussivePaul at 6:54 PM on February 5, 2008