Forum Discussion
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...
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))
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?
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)))