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 907 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.

answered Dec 16, 2022 by
• 63,720 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

## Function to filter excel table data in to a new dataset based on value defined in another table

If you just want to add up ...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

## Excel formula for searching two text in one cell and return values based on the result

You can include a second IF within ...READ MORE

## Formula for inserting a thumbnail picture into excel cell, based on another cell's value

Here is a really excellent tutorial on ...READ MORE