Hi Experts.
It's been a while since I used Excel for anything other than creating printed check sheets, not that I've ever been much of an expert in it anyway. Hopefully what I need is a pretty straightforward solution to competent Excel users.
There is a drive on in the company I work for to have the drivers collect as many boxes for recycling as they can. Each branch will be audited by head office, but my intention is to create a weekly league table to encourage a competitive urge and thus save our planet. So, by answering this you will have contributed towards global ecology ;-)
I've attached a basic sample workbook.
Columns A to D are updated daily with the number of boxes from each route, and are filled in at various times during the day, so the data in columns B, C, and D is not sorted by route. There are a lot more Route numbers than shown as examples in my workbook.
Column C contains the shop number. This is only for double-checking purposes, but this means that Column B will usually contain several entries on subsequent rows for the same Route and not just a single occurrence for each day as indicated.
Therefore column B is not sorted and will continue downwards to many rows.
Columns F and G are for summary purposes, and will provide the data for a simple graph.
In the example, I wish to add formulas to cells G2 to G11 that find values in column B, find the values on the same row in Column D, and total those values in Column D.
Example: Formula in Cell G2 (total for Route 601) should look for all instances of "601" in Column B and, for each one found, locate the value in Column D of that row and accumulate a total in Cell G2.
I've tried quite a few different formulas, but they all need to have the data sorted. Because of the way the workbook is updated, it's not really convenient and I had hoped for a solution as it stands. I could move the Date to another column if needed, but it's laid out logically.
Thanks in anticipation.
Bill
Start Free Trial
