Forum Discussion
Tony2021
Nov 16, 2023Steel Contributor
SumIf and Subtotal with Multiple Criteria
Hello Experts,
I have a table that is filtered.
I need to sum the visible cells only
However, the formula I am using (sumif) sums all the data so it sums the visible and non visible.
I know that subtotal will work but I need to add critiria to it.
The Criteria is sum visible cells if between $x and $y
I hope that makes sense.
I think the file is clearer. Please take a look.
Let me know if you have any questions.
thank you
For example:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(DDProposalFinal[Vendor],ROW(DDProposalFinal[Vendor])-MIN(ROW(DDProposalFinal[Vendor])),,1)),( DDProposalFinal[USD Amount]>$B$13)*(DDProposalFinal[USD Amount]<=$B$14)*DDProposalFinal[USD Amount])
For example:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(DDProposalFinal[Vendor],ROW(DDProposalFinal[Vendor])-MIN(ROW(DDProposalFinal[Vendor])),,1)),( DDProposalFinal[USD Amount]>$B$13)*(DDProposalFinal[USD Amount]<=$B$14)*DDProposalFinal[USD Amount])
- Tony2021Steel ContributorAmazing. that worked perfectly! thank you very much Hans. have a good day.