Combining SUMIFS formula with SUBTOTAL to Sum only filtered results

MrDoesntGetIT 26 Reputation points
2022-02-03T04:18:51.193+00:00

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))

Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,720 questions
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,689 questions
{count} votes

Accepted answer
  1. Lz._ 8,991 Reputation points
    2022-02-03T07:01:54.14+00:00

    @MrDoesntGetIT

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

    170816-screenshot.png

    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

    0 comments No comments

0 additional answers

Sort by: Most helpful