Here is a more detailed breakdown:

MainTable (this is where I am trying to display summed values):

Name | 16-Mar | 23-Mar | 30-Mar |

Joe | [Summed values for Joe during the week of 16-Mar.] | [Summed values for Joe during the week of 23-Mar.] | [Summed values for Joe during the week of 30-Mar.] |

Brad | [Summed values for Brad during the week of 16-Mar.] | [Summed values for Brad during the week of 23-Mar.] | [Summed values for Brad during the week of 30-Mar.] |

Sue | [Summed values for Sue during the week of 16-Mar.] | [Summed values for Sue during the week of 23-Mar.] | [Summed values for Sue during the week of 30-Mar.] |

LookupTable (this is the source table with the data I am referencing):

Name | Project | 16-Mar | 23-Mar | 30-Mar |

Joe | Project A | 5 | 10 | 5 |

Joe | Project B | 5 | 5 | 5 |

Brad | Project A | 20 | 5 | 20 |

Joe | Project C | 15 | 5 | 15 |

Sue | Project A | 10 | 25 | 10 |

Brad | Project B | 5 | 10 | 5 |

Joe | Project D | 15 | 20 | 15 |

Brad | Project C | 15 | 25 | 15 |

Sue | Project B | 30 | 15 | 30 |

Like I said, I've solved this already with a SUMIF formula. For example, this is the formula that would sit in MainTable B2: =SUMIF('LookupTable'!$A:$A, A2,'LookupTable'!$C:$C). That returns a value of 40. I then just copy that formula across each cell in the main table and I get my results for each person, for each week.

My issue is that on the main table, I will hide weeks that are in the past. So the formula works right now, but once we reach March 23rd, I will hide the week starting March 26th. I instead want to create the formula to take into account the actual date in the header row (since I have the date in both tables), but my attempts at figuring that out have proven unsuccessful so far. I have tried this using a SUMIFS formula:

=SUMIFS('LookupTable'!C2:E9, 'LookupTable'!C1:E1, B1, 'LookupTable'!A:A, A2)

But this currently returns an error. I assume I am missing something in my understanding/assumptions, so here is my thinking and understanding of my parameters:

'LookupTable'!C2:E9 ==> this defines the lookup range of values to sum. I have it set to look at all non-name/project/date cells in the lookup table.

'LookupTable'!C1:E1 ==> this defines the first criteria range. This is where I'm looking at the dates.

B1 ==> this defines the first criteria. This should take the value in B1 (16-Mar) and find the matching value in the previous criteria range.

'LookupTable'!A:A ==> this defines the second criteria range. This is where I'm looking at the names.

A2 ==> this defines the second criteria. This should take the value in A2 (Joe) and find rows with matching values in the previous criteria range.

Am I on the right track? I am looking either for feedback to tweak my sumifs formula, if that is on the right track. Or for recommendations if there is a better way to tackle this problem.