Trying to calculate the SUM of my hours worked for individual subjects in a table.

Hi, as the title says I'm trying to calculate the sum of my hours per unit and put it into a column. I am not that experienced with excel but I will be updating this spreadsheet recording my progress throughout the year so I am looking to use formulas rather than code. I have tried combining index and match but that only returns the first piece of data it finds in the table.

I'm sorry if i havent explained it properly, I wrote it all out then accidentally clicked the back button on my mouse, deleting everything bar the title.

Here is the table i would like to use to cross reference the unit codes and input the total amount of hours for each individual unit.

image 1 excel.png

Here is the table that stores my recorded data.

image 2 excel.png

Thank you in advance,

Michael

Welcome to the forum.

For your 'how often', put this in C3 and drag down:

Formula:

Please Login or Register  to view this content.

Change the range $E$14:$E$44 to cover at least the entire range of your data ($E$14:$E$10000 if needed). If you have the data in an Excel Table (which it looks like you have) then you can use a structured table reference instead - it'll look something like this: Table2[[#All],[Unit Short Code]]. If your summary is also in an Excel Table, you can replace the 'A3' with a structured reference as well: [@[Unit Short Code]]. Then, the formula will be:

Formula:

Please Login or Register  to view this content.

In this case, you probably won't need to drag down either - the table will probably automatically fill in down the column (dependent on your exact settings).

For the hours, use one of these (without/with Tables):

Formula:

Please Login or Register  to view this content.

Edit: Format the results using the custom format [h]:mm so that you can count hours past 24 - if you use just h:mm then 25 hrs will display as 1:00.


ps For future reference, it's easier if you can upload a sample file, with some representative (non-confidential) data, instead of pictures. That way we can tailor the answer better to your question.

Hope that helps.

Glad we could help. (Geoff, thanks for jumping in.)

As you've only recently joined, you may not know that you can mark the thread as Solved if your original question has been taken care of so others know there's an answer here. Instructions to do it are in my sig - if you could take a moment to do it, that would be appreciated. Please also consider thanking anyone who helped you by clicking * Add Reputation under the post which helped. Thanks.