Filter sumproduct formula based on array

0 votes

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?

enter image description here

Dec 16, 2022 in Others by Kithuzzz
• 38,020 points
1,078 views

1 answer to this question.

0 votes

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.

enter image description here

answered Dec 16, 2022 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
1 answer

Excel formula to average selected cells based on certain criteria

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

answered Nov 13, 2022 in Others by narikkadan
• 63,720 points
432 views
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,720 points
1,056 views
0 votes
1 answer

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

answered Feb 13, 2023 in Others by narikkadan
• 63,720 points
820 views
0 votes
1 answer
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,690 points
386 views
0 votes
1 answer

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

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

answered Oct 15, 2022 in Others by narikkadan
• 63,720 points
1,839 views
0 votes
1 answer

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

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

answered Oct 17, 2022 in Others by narikkadan
• 63,720 points
2,411 views
0 votes
0 answers

How to create DropDown which have dynamic Validation List

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

Oct 31, 2022 in Others by Kithuzzz
• 38,020 points
545 views
0 votes
1 answer

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

answered Oct 10, 2022 in Others by narikkadan
• 63,720 points
1,167 views
0 votes
1 answer

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

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

answered Oct 31, 2022 in Others by narikkadan
• 63,720 points
1,467 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