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
• 28,900 points
66 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
• 53,520 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
• 53,520 points
74 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
• 53,520 points
75 views
0 votes
0 answers

Write a DAX query to obtain the bottom 5 customers based on the order price

Jan 22, 2020 in Others by anonymous
• 160 points
200 views
0 votes
0 answers

get unique distinct items based on a condition and a date condition

hi could you help me sorry my ...READ MORE

Nov 23, 2021 in Others by adolpo
• 120 points
64 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,630 points
106 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
• 53,520 points
216 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
• 53,520 points
247 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
• 28,900 points
143 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
• 53,520 points
208 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
• 53,520 points
167 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