Filter Function returns #N/A from Dynamic Array but works with cell reference

N

Ngamia

New Member
Joined
Apr 26, 2021
Messages
20
Office Version
  1. 2021
Platform
  1. Windows
  2. MacOS
Hi everyone,

I need assistance on how to correctly reference the dynamic array in the example below. I am using Excel 2021.

The formulas in L2 and M2, give me the maximum and minimum price that each customer paid. When I drag down, the formula works as intended. However, when I change the formula to reference the array by adding the spill range reference, as highlighted below, I get an #N/A error.

=MAX(FILTER(Trades[SALES PRICE],(Trades[CUSTOMER ID]=I2#)*(Trades[ITEM]=J2#)))

ITEMSALES PRICEORDER SIZEZONECUSTOMER ID
TIMBER15.7100WEST27982
BOLTS15.1100EAST9870
TIMBER15.75600WEST35861
TIMBER15.3300WEST27464
BOLTS15.752530WEST5175
TIMBER15.45200WEST20615
TIMBER15.75100WEST30667
BOLTS15.5500EAST9179
TIMBER15.751000WEST1512
BOLTS15.4500EAST5175
TIMBER15.751000WEST31720
TIMBER15.57100WEST21344
NAILS15.2200WEST20615
NAILS15500WEST20615
TIMBER151000WEST31720
TIMBER152500WEST32119
BOLTS15.9500EAST9870
TIMBER151000WEST1512
BOLTS15.3100WEST9870
BOLTS15.3300WEST9179
TIMBER15.35100WEST20615
BOLTS15.5300WEST20615
NAILS21.2400WEST1834


Customer Reconciliation.xlsx
IJKLM
1CUSTOMER IDITEMTOTAL ORDERSHIGHEST PRICELOWEST PRICE
29870BOLTS60015.915.1
39179BOLTS500
45175BOLTS500
Sheet1
Cell Formulas
RangeFormula
I2:I4I2=UNIQUE(FILTER(Trades[CUSTOMER ID],(Trades[ITEM]="BOLTS")*(Trades[ZONE]="EAST")))
J2:J4J2=IF(ISNUMBER(I2#),"BOLTS",0)
K2:K4K2=SUMIFS(Trades[ORDER SIZE],Trades[CUSTOMER ID],"="&I2#,Trades[ITEM],"="&J2#,Trades[ZONE],"EAST")
L2L2=MAX(FILTER(Trades[SALES PRICE],(Trades[CUSTOMER ID]=I2)*(Trades[ITEM]=J2)))
M2M2=MIN(FILTER(Trades[SALES PRICE],(Trades[CUSTOMER ID]=I2)*(Trades[ITEM]=J2)))
Dynamic array formulas.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
Plesae try this formula

=MAX(FILTER(Trades[SALES PRICE],(Trades[CUSTOMER ID]=I2)*(Trades[ITEM]=J2)))

And similarly for the minimum price formula:

=MIN(FILTER(Trades[SALES PRICE],(Trades[CUSTOMER ID]=I2)*(Trades[ITEM]=J2)))
 
Upvote 0
Guna13 said:
Plesae try this formula

=MAX(FILTER(Trades[SALES PRICE],(Trades[CUSTOMER ID]=I2)*(Trades[ITEM]=J2)))
Click to expand...
Isn't that the exact same formula that the OP has already tried?
Ngamia said:
1690963299164.png
Click to expand...

Ngamia said:
I am using Excel 2021.
Click to expand...
If that is the case then I'm not sure you can do it with a spill formula. Your profile also lists MS 365 & it can certainly be done with that version

Ngamia.xlsm
ABCDEFIJKLM
1ITEMSALES PRICEORDER SIZEZONECUSTOMER IDCUSTOMER IDITEMTOTAL ORDERSHIGHEST PRICELOWEST PRICE
2TIMBER15.7100WEST279829870BOLTS60015.915.1
3BOLTS15.1100EAST98709179BOLTS50015.515.3
4TIMBER15.75600WEST358615175BOLTS50015.7515.4
5TIMBER15.3300WEST27464
6BOLTS15.752530WEST5175
7TIMBER15.45200WEST20615
8TIMBER15.75100WEST30667
9BOLTS15.5500EAST9179
10TIMBER15.751000WEST1512
11BOLTS15.4500EAST5175
12TIMBER15.751000WEST31720
13TIMBER15.57100WEST21344
14NAILS15.2200WEST20615
15NAILS15500WEST20615
16TIMBER151000WEST31720
17TIMBER152500WEST32119
18BOLTS15.9500EAST9870
19TIMBER151000WEST1512
20BOLTS15.3100WEST9870
21BOLTS15.3300WEST9179
22TIMBER15.35100WEST20615
23BOLTS15.5300WEST20615
24NAILS21.2400WEST1834
25
26
Sheet1
Cell Formulas
RangeFormula
I2:I4I2=UNIQUE(FILTER(Trades[CUSTOMER ID],(Trades[ITEM]="BOLTS")*(Trades[ZONE]="EAST")))
J2:J4J2=IF(ISNUMBER(I2#),"BOLTS",0)
K2:K4K2=SUMIFS(Trades[ORDER SIZE],Trades[CUSTOMER ID],"="&I2#,Trades[ITEM],"="&J2#,Trades[ZONE],"EAST")
L2:L4L2=LET(a,HSTACK(I2#,J2#),BYROW(a,LAMBDA(rw,MAX(FILTER(Trades[SALES PRICE],(Trades[CUSTOMER ID]=INDEX(rw,1))*(Trades[ITEM]=INDEX(rw,2)))))))
M2:M4M2=LET(a,HSTACK(I2#,J2#),BYROW(a,LAMBDA(rw,MIN(FILTER(Trades[SALES PRICE],(Trades[CUSTOMER ID]=INDEX(rw,1))*(Trades[ITEM]=INDEX(rw,2)))))))
Dynamic array formulas.
 
Upvote 0
Peter_SSs said:
If that is the case then I'm not sure you can do it with a spill formula. Your profile also lists MS 365 & it can certainly be done with that version
Click to expand...

Thanks for this Peter.

Unfortunately, while I have access to MS 365, the team that I am creating this for only has access to Office 2021.
Might there be another way to approach it that does not necessarily use the Filter function?
 
Upvote 0
Hmm, my previous suggestion was a bit over the top by the look of it. :oops:
What about this?

Ngamia.xlsm
ABCDEFIJKLM
1ITEMSALES PRICEORDER SIZEZONECUSTOMER IDCUSTOMER IDITEMTOTAL ORDERSHIGHEST PRICELOWEST PRICE
2TIMBER15.7100WEST279829870BOLTS60015.915.1
3BOLTS15.1100EAST98709179BOLTS50015.515.3
4TIMBER15.75600WEST358615175BOLTS50015.7515.4
5TIMBER15.3300WEST27464
6BOLTS15.752530WEST5175
7TIMBER15.45200WEST20615
8TIMBER15.75100WEST30667
9BOLTS15.5500EAST9179
10TIMBER15.751000WEST1512
11BOLTS15.4500EAST5175
12TIMBER15.751000WEST31720
13TIMBER15.57100WEST21344
14NAILS15.2200WEST20615
15NAILS15500WEST20615
16TIMBER151000WEST31720
17TIMBER152500WEST32119
18BOLTS15.9500EAST9870
19TIMBER151000WEST1512
20BOLTS15.3100WEST9870
21BOLTS15.3300WEST9179
22TIMBER15.35100WEST20615
23BOLTS15.5300WEST20615
24NAILS21.2400WEST1834
25
26
Sheet1
Cell Formulas
RangeFormula
I2:I4I2=UNIQUE(FILTER(Trades[CUSTOMER ID],(Trades[ITEM]="BOLTS")*(Trades[ZONE]="EAST")))
J2:J4J2=IF(ISNUMBER(I2#),"BOLTS",0)
K2:K4K2=SUMIFS(Trades[ORDER SIZE],Trades[CUSTOMER ID],"="&I2#,Trades[ITEM],"="&J2#,Trades[ZONE],"EAST")
L2:L4L2=MAXIFS(Trades[SALES PRICE],Trades[CUSTOMER ID],I2#,Trades[ITEM],J2#)
M2:M4M2=MINIFS(Trades[SALES PRICE],Trades[CUSTOMER ID],I2#,Trades[ITEM],J2#)
Dynamic array formulas.
 
Upvote 0
Solution
This works perfectly Peter. Thank you very much for your assistance.

I have never encountered the MAXIF function before so I have learnt 2 things from you.
 
Upvote 0
You're welcome. Glad it worked for you. Thanks for the follow-up. :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
Dynamic Array Formula Problem
Replies
5
Views
137
KRice
K
RICH937
  • Solved
Using Dropdowns to filter Dynamic Array
Replies
3
Views
395
Georgiboy
Georgiboy
T
  • Solved
Combining Data from Multiple Sheets
2
Replies
16
Views
856
alansidman
A
T
  • Question
Flattening a hierarchy from 5 columns into a unique one
Replies
1
Views
473
Kerryx
K
C
  • Question
Mutiple criteria filtering to separate 2NF table (nested FILTER over FILTER formula?)
Replies
1
Views
348
Fluff
Fluff

Forum statistics

Threads
1,215,148
Messages
6,123,301
Members
449,095
Latest member
Chestertim

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top