Glad to help. About the formula, the messiest part involves the SUMPRODUCT function. In it, you are essentially creating four arrays using logical checks, each array indicating which rows satisfy one particular condition:
ISNUMBER(FIND($B5,'2024_Offering'!$B$2:$B$750)) ...searches for the text in B5 in the range '2024_Offering'!$B$2:$B$750 and returns a number indicating the character position where the match occurs on each row; and if no match is found, then a #VALUE! error is created. So this gives an array of 749 elements (the row range runs from 2 to 750, which is 749 elements) that are either a number or a #VALUE! error. The numbers are indicative of a match, so those rows need to be considered (and used if all other criteria are met). To convert this 749 element array to something more useful, it is operated on by the ISNUMBER function to return either TRUE where an array element is a number or a FALSE everywhere else, including the errors.
('2024_Offering'!$C$2:$C$750=$E$1) ...creates a 749 element array indicating which cells in '2024_Offering'!$C$2:$C$750 match the name in $E$1, and each element is either TRUE or FALSE
('2024_Offering'!$A$2:$A$750>=$C$1) ...creates a 749 element array indicating which cells in '2024_Offering'!$A$2:$A$750 contain dates that occur on or after the "From" date in $C$1, and each element is either TRUE or FALSE
('2024_Offering'!$A$2:$A$750<=$C$2) ...creates a 749 element array indicating which cells in '2024_Offering'!$A$2:$A$750 contain dates that occur on or before the "To" date in $C$2, and each element is either TRUE or FALSE
When these arrays are multiplied together, the result is a single 749 element array. The arithmetic operation (multiplication in this case since you want all four conditions to be true simultaneously) will automatically coerce any TRUEs to assume a value of 1, and any FALSEs to assume a value of 0. So the end result is an array of 1's and 0's.
Then the SUMPRODUCT function reduces to something like this:
SUMPRODUCT( {resultant array of 1's and 0's}, '2024_Offering'!$D$2:$D$750 )
...which multiples each item in '2024_Offering'!$D$2:$D$750 by either 0 (representing rows where at least one condition is not satisfied) or 1 (representing rows where all conditions are satisfied).