Forum Discussion

Rio01's avatar
Rio01
Occasional Reader
Oct 05, 2025

SUM WITH INDEX MATCH ERROR

so i want to sum all of possibility based on criteria to search value that i've adjust. the goal is to create dynamic formula that dont need to adjust per column (that's why i adjust the range formul...
excel
Formulas and Functions
Harun24HR's avatar
Harun24HR
Bronze Contributor
Oct 06, 2025

You need nested FILTER() then SUM(). Try-

=SUM(FILTER(FILTER(C5:N9,(C1:N1=C13)*(C2:N2=C14),0),(A5:A9=C15)*(B5:B9=C16),0))

Download the sample file.

  • Rio01's avatar
    Rio01
    Occasional Reader
    Oct 06, 2025

    Hi, this actually works for me. Yet, after selecting it into more larger data for example range C5:N99999 it returns error. is there any limitation on this formula for larger data set?

    • Harun24HR's avatar
      Harun24HR
      Bronze Contributor
      Oct 07, 2025

      Would you please try SUMPRODUCT() and observe performance. I believe, it would be much faster than FILTER().

      =SUMPRODUCT((C5:N9*(C1:N1=C13)*(C2:N2=C14))*((A5:A9=C15)*(B5:B9=C16)))

       

Resources