Combining SUMIFS formula with SUBTOTAL to Sum only filtered results

Question

Hi Everyone,

I have a query table I have built for one of our team to pull data from a sharepoint list containing people work hours. I have used a SUMIFS formula to output sum totals of "Converted Hours" for each unique username "Name".

They now want to be able to use this same format to filter the query for date ranges and only sum the hours for the filtered results. I have been playing all morning but I have not been able to get the subtotal function working within the SUMIFS formula. The formula below will only SUM total hours in the query despite any filters applied to the query results

Can anyone advise what I should be doing here? Thanks for your assistance!

=IF(ISBLANK(B4),"",SUMIFS(Table_query__1[[#All],[Converted Hours]],Table_query__1[[#All],[Name]],"="&B4))

Answer 1

@MrDoesntGetIT

In the meantime a basis example
Table1 is filtered (Name1 & Name2 only) and we want the SUM of [Hrs] >= date value in C2

Formula in C3:

=SUMPRODUCT(  
    SUBTOTAL(3,OFFSET(Table1[Date],ROW(Table1)-ROW(Table1[#Headers])-1,,1)),  
    --(Table1[Date] >= C2),  
    Table1[Hrs]  
)  

Or with Excel Web/365/2021:

=SUMPRODUCT(  
    SUBTOTAL(3,OFFSET(Table1[Date],SEQUENCE(ROWS(Table1),,0),,1)),  
    --(Table1[Date] >= C2),  
    Table1[Hrs]  
)  

NB: In above formulas the OFFSET is done on Table1[Date] but can be done on any other column of the table assuming there's no blanks/empty cells in that column

Corresponding sample workbook avail. here for download