Adding a Lookup Formula to Sumif formula's "range"

[Eloise7985]

Good day all,

I'm new to these Forums.

I hope you will understand my question.

I have to submit a log of my hours worked in certain aspects of my profession to our local board in order to be accredited with a professional practice number.
I have made a time sheet that I log the hours I work on different tasks. (which works fine)

I want to summarize this time sheet to show how many hours I have worked on each task in a different spreadsheet.
Each tab in my spread sheet has the months of the year and then in the sheet there are the weeks listed at top and tasks on the left.

Now, what I want to do is have excel look up the week(range), find the corresponding task(criteria) and sum the hours of that specific tasks in that week only.

i.e in week 1 I spent 8 hours meeting with clients and so on.

I originally used a standard sumif formula. but had to manually reselect the corresponding week's range to sum, which is time consuming.

I have attached the 2 spreadsheets.

[Aardigspook]

Welcome to the forum.

Edit: Before you do any of what's below, re-save your 'SACAP Eloise' file in .xlsx format - the formula below isn't available in pre-2007 file versions.

You can use SUMIFS, but first you need to do a couple of things.
1. Check that the wording in column A of the SACAP Eloise 2018 file is exactly the same as in column J of the Time Sheet Studio121 2018 file. In A12, statutory is mis-spelled.
2. Un-merge all the cells in column A of the Time Sheet Studio121 2018 file and enter the week label in each row. So, 'Week 1' in cells A11:A17, 'Week 2' in cells A18:A26, etc.
(It's a good rule to avoid merged cells when you're wanting to use complicated formulae - they mess up the ranges.)
3. Format all the time cells in the SACAP Eloise 2018 file (columns B to F) with the Custom number format [h]:mm. If you don't want zeroes to show, then use this Custom format instead: [h]:mm;[h]:mm;;@ (or [h]:mm;[h]:mm;;@ if you want a dash (-) to show for zeroes). The times will now show as 9:30, etc.
4. Have both files open, saved in the same folder.

Now, in cell B3, enter this formula:

Please Login or Register  to view this content.

What it does is Sum column G in the time sheet file but only if both the criteria are met: time sheet col A is equal to SACAP row 2 (the week) and time sheet col J is equal to SACAP column A (the task description).

Hope that helps.