Forum Discussion

dianakim's avatar
dianakim
Copper Contributor
Oct 06, 2023

How can I get sum vale of filter data?

Hi all, after filter period I want to get values of H5 which meet with project1 and S.

 

I've tried combination of subtotal and sum formula like below but it doesn't work because there are more values of period in original data. 

=(SUBTOTAL(9,R22:R99999)-(SUMIFS(R$21:R$99999,$E$21:$E$99999,$O12,$J$21:$J$99999,$Q12)+SUMIFS(R$21:R$99999,$E$21:$E$99999,$O12,$J$21:$J$99999,$Q12&"_N")+SUMIF($E$21:$E$99999,$O16,R$21:R$99999))

 

excel
Excel for web

3 Replies

Sort By
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Oct 06, 2023

    dianakim 

    A 365 solution without OFFSET:

    =LET(
    visible, LAMBDA(project, deta, amount,
        IF(AND(project = E1, deta = F1, SUBTOTAL(3, amount) = 1), amount, 0)
    ),
    SUM(MAP(Table1[Project], Table1[deta], Table1[amount], visible))
)
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Oct 06, 2023

    dianakim 

    To get the sum of Amount" for "Project1" and "S" in Cell H5 you can use the following formula:

    =SUMPRODUCT(SUBTOTAL(109,OFFSET(D21:D99999,ROW(D21:D99999)-MIN(ROW(D21:D99999)),,1)), --(B21:B99999="Project1"), --(B21:B99999="S"))

     

    To get the sum of "Amount" for "Project1" and "R" in Cell H6, you can use the following formula:

    =SUMPRODUCT(SUBTOTAL(109, OFFSET(D21:D99999, ROW(D21:D99999)-MIN(ROW(D21:D99

     

    untested.

    Hope this will help you

Resources