Forum Discussion

Tony2021's avatar
Tony2021
Steel Contributor
Nov 16, 2023
Solved

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

 

  • Tony2021 

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

  • Tony2021 

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

    • Tony2021's avatar
      Tony2021
      Steel Contributor
      Amazing. that worked perfectly! thank you very much Hans. have a good day.

Resources