Filter sumproduct formula based on array

Question

Using Table 1, I can use the SUMPRODUCT function to determine the total revenue. However, I would prefer to have a straight filtering option in the formula to exclude certain places. When region B is removed from the data using the formula below, the proper result (13,000), it is produced is:

=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?

Answer 1

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.