I have to do a daily Excel spreadsheet, in an IT troubleshooting environment, which lists issues for the day and week, whether they are closed or open, and, for the closed issues, which of a half-dozen categories the resolutions fall into. The detail section comprises three parts–(1) issues currently open, (2) issues that have been closed today, and (3) issues closed since the beginning of the week, with the contents of (2) repeated.
Above the detail section is a set of totals based on the above, which we now do manually.
For each detail line I can create several numeric columns that populate automatically with 1 or 0 depending on what’s in the Resolution column. Over the entire spreadsheet, then, I could easily tell how many of the closed issues are, say, “Configuration” or “Data Setup” incidents, but the problem is, I need to be able to generate these totals only over certain vertical segments of the report, based on which of the three detail sections they fall, that I described above. I do know that I can name a cell on the horizontal divider “bars” and from that I can use the ROW function to get the row number for that row. But how can I then use the information provided by the named cells in the divider bars to determine which rows of a given column should be added?
The MATCH and INDEX functions are your friends … Try something like this:
=SUM(INDEX(A1:B1000,MATCH(“Bar1”,A1:A1000,FALSE)+1,2):INDEX(A1:B1000,MATCH(“Bar2”,A1:A1000,FALSE)-1,2))
(I love those, I use them for everything. I used Excel for years before I learned about VLOOKUP and HLOOKUP, then I wondered why people seemed to like them so much. MATCH, INDEX, and INDIRECT, actually, you can do anything with those.)
I’m having a hard time picturing your setup, so forgive me if I’m misunderstanding. From what I can tell, another strategy you can use is the DSUM function. It requires the list name or reference, which field you’re trying to total, and what criteria you’re using. The criteria parameter is a separate cell reference that contains your criteria as a list. I started using Excel’s database functions a lot after I got past the initial learning curve.
I love Excel, but finding aggregates by criteria is when I miss a real database.
He’d need to add a column to work it that way, showing which category each row fell into. Which could be done, but might mess up needed formatting or exporting, and would be an additional step for every update.
And yes, of course the ideal way to do this would be to set up a database. (We like databases! )
This part I’ve already done, and it works. I defined a column for each category (there aren’t that many), then use a condition on the same row to populate each with 0 or 1. I don’t believe exporting should be an issue in this case.
You’d need to add another column, showing the category you need to divide the sums over. The column would read ‘Currently Open’, ‘Closed Today’, etc. Then you could use SUMIFs (or even COUNTIFS, and get rid of the 1 and 0 columns), or just make a pivot table … Keeping it correctly updated might be a nightmare, though.
Honestly, it’d be very hard (to me) to talk someone through the general methods for building the right formulae. I can’t see the layout & data you’re working with … But I’m pretty sure that, with properly-sorted rows, MATCH and INDEX can give you what you need. MATCH returns the row or column of the first matching cell in a range. INDEX returns a cell reference given a range, a row number, and a column number. The combination can do some surprisingly powerful things.
)