Google sheet formula to return sum based on multiple criteria using input cells

user_15593937 0
Viewed 38

I have rows of dates with tasks measured in hours. These tasks are assigned to different team leaders whose names are also included on each row. I would like create a multiple criteria Google sheet formula that returns the sum of hours based on the date range and the name of the team leader.

These are the data input cells I would be entering to produce the sum:

Date Start: Date End: Team Leader Name:

Ideally if the Team Leader name was not entered, the formula would sum the hours for all of the rows selected by the date range.

Here are some sample rows:

Job Date Hours Team Leader
03/25/2022 8 John
04/22/2022 7 Hannah
04/23/2022 6 John
05/01/2022 6 Hannah

Thank you in advance for your help with this!

google-sheets-formula
2 Answers

Assuming you have the following

  • A:C - Job Date, Hours, Team Leader
  • D1 - Start Date
  • D2 - End Date
  • D3 - Team Leader
=QUERY(
  {A1:C},
  "select Sum(Col2)
   where
    Col1 >= date '"&TEXT(IF(ISBLANK(D1),DATE(1970,1,1),D1),"yyyy-mm-dd")&"' and
    Col1 <= date '"&TEXT(IF(ISBLANK(D2),DATE(3000,1,1),D2),"yyyy-mm-dd")&"' and 
    Col3 matches '"&IF(ISBLANK(D3),".*",D3)&"'
   label Sum(Col2) ''")

If the start or end dates are blank, it reverts to extreme dates. For Col3 it is using REGEX -- if D3 is blank, it reverts to a wildcard and will return everything summed up.

user_122540980

You can use SUMIFS() function like-

=SUMIFS(B2:B,A2:A,">="&E2,A2:A,"<="&F2,C2:C,IF(G2="","*",G2))

enter image description here

user_55147470
Related