Forum Discussion

Tony2021's avatar
Tony2021
Iron 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...
BI & Data Analysis
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Nov 16, 2023

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

HansVogelaar's avatar
HansVogelaar
MVP
Nov 16, 2023

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
    Iron Contributor
    Nov 17, 2023
    Amazing. that worked perfectly! thank you very much Hans. have a good day.

Resources