Automatic way to select a range for a formula based on the date

Question

I am creating a calculator where the user describes a list of tasks in Activities!B2:B, and in C2:C they rate 0-5. To separate each day, I've used a date for each task in A2:A (stylistically separated by a black bar).

In Budget!B2, the totals for column C on Activites are added for that day and then divided by 2. In Budget!K, I've used a sumif formula that takes the number from B2 and subtracts the sum of that day if the task has been checked off.

This is the current formula in Budget!K2:

=B2-sumif(Activities!H2:H18,TRUE,Activities!C2:C18)

This part is doing what it's supposed to. I want to know if there's a way to have it read Activities!A:A with the dates, and automatically use the rows from one date while calculating the total left from checked items like it's currently doing.

Right now, I'm having to manually put in the range for that date (i.e., H2:H18, C2:C18), which is a bit tedious.

I've played around with sumifs and &, but I haven't been able to make anything work.

Energy Budget Calculator Project Link

Answer 1

Use filter(), like this:

=map($A2:$A, B2:B, lambda(date, budget, 
  if( 
    isnumber(date) * isnumber(budget), 
    budget - 
    sum(iferror(filter( 
      Activities!C2:C, 
      Activities!$A2:$A = date, 
      Activities!$H2:$H 
    ))), 
    iferror(ø) 
  ) 
))

Clear the range K2:O, put the formula in cell K2, and copy the formula cell to the right.

See map(), lambda(), filter() and iferror().