In Excel, how can I filter, pull, and sum values from a column if their rows match data in another table and are within a specific date range?

Question

Okay, so for context, this link got me started but I am stuck now attempting to apply it to my use case: Excel - sum values based on a column that match another column in another table.

I have a hourly log where I enter in my work, including the date, project and task information that it will get billed to, and the start and end time for the hours worked, which calculates how many hours I've worked from the time entered (rounded to the nearest 15 minutes).

Here's (approximately) what the log ("timeLog") looks like with example data:

	A	B	C	D	E
1	Date	Project and Task	Start Time	End Time	Hours
2	1/1/2023	Project 1:Task 1	8:00 AM	10:00 AM	2
3	1/1/2023	Project 2:Task 1	10:00 AM	4:00 PM	6
4	1/6/2023	Holiday Hours	8:00 AM	4:00 PM	8
5	1/7/2023	Sick Time	8:00 AM	12:00 PM	4
6	2/6/2023	Project 1:Task 1	8:00 AM	4:00 PM	8

   ^{[Transcribed from this image.]}

On another sheet, I have a table that lists all projects and associated tasks ("Project and Task ID" column). Each project has an associated type (namely, worked hours, holiday, sick, or vacation).

Here's what the project info table ("projectTable") looks like:

	A	B
1	Project and Task ID	Type ID
2	Project 1:Task 1	Worked
3	Project 2:Task 1	Worked
4	Holiday Hours	Holiday
5	Sick Time	Sick

      ^{[Transcribed from this image.]}

For the purposes of calculating how much sick/vacation I accrue, I need to pull out how many hours I have worked in a given billing period. However, I also need the overall hours I submit, including sick/vacation/holiday, to remain in the hourly log because I use those for overall FTE calculations.

Here's what I am currently using to sum all hours in a billing period (12/26/22-1/25/23) that are associated with a project with the "worked" type ID:

=SUM(IFERROR(IF(COUNTIFS(projectTable[Project & Task ID],FILTER(timeLog[Project and Task],(timeLog[Date]<=DATE(2023,1,25))*(timeLog[Date]>DATE(2022,12,25)),0),projectTable[Type ID],"Worked")>0,timeLog[Hours],0),0))

First, I use FILTER to get all table entries for the specified period.

FILTER(timeLog[Project and Task],(timeLog[Date]<=DATE(2023,1,25))*(timeLog[Date]>DATE(2022,12,25)),0)

Then, COUNTIFS checks that to see if any projects are of the "Worked" TypeID:

COUNTIFS(projectTable[Project & Task ID],FILTER(timeLog[Project and Task],(timeLog[Date]<=DATE(2023,1,25))*(timeLog[Date]>DATE(2022,12,25)),0),projectTable[Type ID],"Worked")

Then, IF returns the hours value for each of those rows, IFERROR 'fixes' an error I kept getting, and SUM computes the total "Worked" hours for the period.

However, this doesn't feel like the best way to do this—it's fragile, returns errors I don't expect, and overall I feel like I'm going about this the wrong way.  What is a better way to do this?

Answer 1

I haven’t studied your solution, but, at a glance, I agree that it seems to be unnecessarily complicated.  I suggest that you add a sixth column to your hourly log, containing

=VLOOKUP(B2, projectTable!$A$2:$B$5, 2, FALSE)

This will translate the “Project and Task” from the hourly log to the “Type ID” from the project info table, as follows:

	A	B	C	D	E	F
1	Date	Project and Task	Start Time	End Time	Hours	Type ID
2	1/1/2023	Project 1:Task 1	8:00 AM	10:00 AM	2	Worked
3	1/1/2023	Project 2:Task 1	10:00 AM	4:00 PM	6	Worked
4	1/6/2023	Holiday Hours	8:00 AM	4:00 PM	8	Holiday
5	1/7/2023	Sick Time	8:00 AM	12:00 PM	4	Sick
6	2/6/2023	Project 1:Task 1	8:00 AM	4:00 PM	8	Worked

It then becomes a simple matter of using COUNTIFS and SUMIFS.  For example, somewhere else (for example, A8:B10), enter the types in A8:A10, enter

=SUMIFS($E$2:$E$6, $A$2:$A$6,">"&DATE(2022,12,25), $A$2:$A$6,"<"&DATE(2023,1,23), $F$2:$F$6,A8)

in B8, and drag/fill down:

	A	B
8	Worked	8
9	Holiday	8
10	Sick	4

Of course the hard-coded dates can be replaced by cell references.

---

Note that I answered a vaguely similar, but more complex, question eight years ago.