Ignore empty filter criteria in SUMPRODUCT formula

0 votes
  |      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, 2023 in Others by Kithuzzz
• 38,010 points
629 views

1 answer to this question.

0 votes
=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)
answered Feb 13, 2023 by narikkadan
• 63,700 points

Related Questions In Others

0 votes
1 answer

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

answered Nov 25, 2022 in Others by narikkadan
• 63,700 points
882 views
0 votes
1 answer

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

answered Dec 16, 2022 in Others by narikkadan
• 63,700 points
853 views
0 votes
0 answers

MS Excel - SumProduct formula with Loop

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

Feb 18, 2022 in Others by Edureka
• 13,670 points
300 views
0 votes
1 answer

Scan with filter returns zero results in DynamoDB

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

answered Mar 4, 2022 in Others by gaurav
• 23,260 points
1,027 views
0 votes
1 answer

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

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
2,123 views
0 votes
1 answer

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

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

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,635 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

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

Feb 17, 2022 in Others by Edureka
• 13,670 points
797 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

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

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
2,826 views
0 votes
1 answer

Formula in criteria range in SUMIFS

No, that kind of manipulation doesn't work ...READ MORE

answered Sep 30, 2022 in Others by narikkadan
• 63,700 points
360 views
0 votes
1 answer

Return empty cell from formula in Excel

There is no way to do this ...READ MORE

answered Nov 20, 2022 in Others by narikkadan
• 63,700 points
4,603 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP