Given Table 1, I am able to calculate the sum of the revenue with the SUMPRODUCT formula. Though, I would like to be able to filter out specific areas directly in the formula. The formula listed below gives the correct result (13,000) when area B is filtered out of the data with:
=SUMPRODUCT(--(Sales[Area]<>Exceptions[Area]);Sales[Quantity];Sales[Price per unit])
However, when I add another area in Table 2, the formula returns an error. Is it possible to filter out multiple variables (areas) directly in the formula?
>Solution :
Use ISERROR(MATCH()):
=SUMPRODUCT(--(ISERROR(MATCH(Sales[Area];Exceptions[Area];0)));Sales[Quantity];Sales[Price per unit])
--(ISERROR(MATCH(Sales[Area];Exceptions[Area];0))) will return 1 if the area is not found in the search area, because the MATCH will return an error when it is not found in the list.
