Sum column if between two separate dates

[ihaveanexcelquestion]

In a project budget, I have an activity start date, end date, and a series of names associated. The data associated with each name is the number of hours they are budgeted for a particular task. I have a calculated field for each name showing the budgeted amount of time each day to spend on an activity.

Activity	Start date	End date	Name 1	Name 2	Name etc.		Name 1 Calc Field - Hours per day	Name 2 Calc Field - Hours per day
Activity A	1/1/2022	1/15/2022	5				=5 / (End Date - Start Date)
Activity B	1/10/2022	2/4/2022	10	5			=10 / (End Date - Start Date)	=5 / (End Date - Start Date)

I have a separate table where in Column A it is every date in the range listed (from minimum start date to maximum end date). In Row 1 I also have all the names mentioned above.

Date	Name 1	Name 2	Name etc.
1/1/2022	Need formula here that sums budgeted hours for Name 1 across all activities for this date
1/2/2022
Etc.

I would like to sum the total budgeted hours for each name across all activities. I'm having trouble with the sum formula that will return this value.

 

[SanjayGMusafir]

Assuming

• one is Table1
• other is Table2 and
• Column for Name1 Calc Field in Table1 is H
• Table2 starts from Cell A1

Try this -

=SUMIFS(H:H,B:B,">="&$A2,C:C,"<="&$A2)

=SUMIFS(H:H,B:B,">="&$A2,C:C,"<="&$A2)

• The above $A2 is cell reference of Table2
• Remember to correct cell references before using the formula

Check if it works the intended way else we can further refine it to the needs.