Pivot tables were meant for this stuff.
If you’ve got Excel 2007 or later, then the first thing you want to do is turn your datasource into an Excel Table. To do this, select your original data, and push Ctrl + T, make sure the ‘My Table has headers’ checkbox is checked, the click OK.
(Aside: the reason we use a table is that they auto expand if you put new data at the bottom of them).
Then select a cell in that table, and click “Insert Pivot Table” from the ribbon, then click OK.
Then in the PivotTable Field list that appears on the right hand side, drag ‘Rate’ down to the ‘Row Labels’ section, and drag ‘Hours’ to the ‘Values’ section.
All done.
Any time you add new data to the table, just right click over the pivot table and select 'Refresh'.
Probably best you do a bit of reading on the web about pivottables. They are the best think about Excel.
See attached.
Book1.xlsx
Bookmarks