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? Dec 16, 2022 in Others 101 views

## 1 answer to this question.

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. • 58,640 points

## Excel formula to average selected cells based on certain criteria

Try: =AVERAGE(IF(X:X=A1,Z:Z)) With Ctrl+Shift+Enter. READ MORE

## In a excel formula I need to create a list of names on one sheet based upon criteria/data of another sheet

The final formula is: =IF(ROWS(\$H\$3:H3)<=\$I\$1,INDEX(Personnel! ...READ MORE

## Ignore empty filter criteria in SUMPRODUCT formula

=SUMPRODUCT((\$C\$2:\$C\$7)*(IF(E1="",1,(\$A\$2:\$A\$7=E1))*(IF(E2="",1,\$B\$2:\$B\$7=E2)))) Replaces the condition with 1 (all values) in case E1 and/or E2 is ...READ MORE

## MS Excel - SumProduct formula with Loop

1 I have 4 arrays of data where ...READ MORE

## In excel how do I reference the current row but a specific column?

Put a \$ symbol in front of ...READ MORE

## Excel function for divide or split number to maximum possible equal parts

The underlying math for this is as ...READ MORE

## How to create DropDown which have dynamic Validation List

In my data table, the columns "Category" ...READ MORE