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?
posted by korres to computers & internet (4 answers total)
(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:
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.