Formula to Sum the values of a column based on multiple criteria

[PAO1609]

Hello, I need to create a formula to sum the values of a column based on two different criteria's, but here's a trick to it that i cant figure out. This is an example of the table Here, I have the sum of al "Valor1" which is 18


But, i need that sum to be dynamic so it shows me the sum of all "Material" filter, for example, if i only filter "Mat1" i should get 10, i know i can do that with subtotal(109), but that only works if i have that same row visible, i need to have some sort of resume of all values on Material even if not all cells are visible, this is an example of what i need.

As you can see, i only have two rows of Mat1 but i get all values related to that value, i need to be able to filter multiple Materials and get the sum of all of them

 

[AFPathfinder]

Would SUBTOTAL(109) work, except that it won't include hidden data? If so, try SUBTOTAL(9). It's the same thing, but function_num 9 includes hidden.

SUBTOTAL Function

If I'm off the mark, let me know.