# How can I get sum vale of filter data?

Copper Contributor

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

3 Replies

# Re: How can I get sum vale of filter data?

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.

# Re: How can I get sum vale of filter data?

Hi,

You may try following as one type of solution:

# Re: How can I get sum vale of filter data?

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