```  |      A      |      B      |      C      |          D        |     E
--|-------------|-------------|-------------|-------------------|---------------
1 | Product     |   Brand     |   Revenue   |  Filter Product   |   Product A
2 | Product A   |   Brand 1   |     500     |  Fitler Brand     |   Brand 1
3 | Product A   |   Brand 2   |     600     |  Result           |   500
4 | Product B   |   Brand 2   |     400     |                   |
5 | Product C   |   Brand 3   |     350     |                   |
6 | Product C   |   Brand 1   |     800     |                   |
7 | Product C   |   Brand 1   |     700     |                   |
```

In Cell E3 I want to sum the revenue in Column C based on the criterias that are entered in Cell E1 and Cell E2. Therefore, I implemented this formula which works perfectly.

```=SUMPRODUCT((\$C\$2:\$C\$7)*(\$A\$2:\$A\$7=E1)*(\$B\$2:\$B\$7=E2))
```

However, now I want to implement in the formula a logic that if Cell E1 or Cell E2 is empty those filters are ignored.
For example, if Cell E1 is empty and Cell E2="Brand 1" the result would be 2.000 (500+800+700)

Therefore, I tried to modify the formula like this:

```=SUMPRODUCT((\$C\$2:\$C\$7)*(\$A\$2:\$A\$7=IF(E1="","*",E1))*(\$B\$2:\$B\$7=IF(E2="","*",E2)))
```

However, this formula gives me back 0 and not the expected 2.000.

How do I need to adjust it to make it work?

Feb 13 in Others 63 views

## 1 answer to this question.

`=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 empty, using IF.

In Office 365 you could use this formula:

`=IFERROR(SUM(FILTER(C2:C7,(IF(E1="",1,A2:A7=E1))*(IF(E2="",1,+B2:B7=E2)))),0)`
• 63,000 points

## 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

## Filter sumproduct formula based on array

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 ...READ MORE

## MS Excel - SumProduct formula with Loop

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

## Scan with filter returns zero results in DynamoDB

By accessing all items in a table ...READ MORE

## Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

## Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

## IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE