Ignore empty filter criteria in SUMPRODUCT formula

Question

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

Answer 1

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