October 22, 2014
Hello All,
This is my first post and I hope I can explain what I need to accomplish.
In the attached sheet I have an array formula that extracts a list from my data based on 5 criteria. It works perfectly but there is only one possible match based on that criteria.
Where I am needing assistance is an array formula based on 4 criteria, but extracting an unique list with corresponding amounts. I have tried using my original formula with a frequency component but have failed.
Thank you,
Deena
July 16, 2010
Hi Deena,
Thanks for providing your file. It makes it much easier to answer your question.
When I see array formulas like this my instant reaction is, you don't need a formula, you need a PivotTable. See attached file columns Q & R. The PivotTable is a much more robust and simpler solution.
Please avoid array formulas when a PivotTable will do the job. Array formulas are a problem waiting to happen.
Mynda
July 3, 2016
Hi All,
in J35 array entered before to be copied below:
=IFERROR(INDEX(E$2:E$2000,SMALL(IF(FREQUENCY(IF((B$2:B$2000=$I$30)*(C$2:C$2000+0=$J$30)*(D$2:D$2000=$K$30)*(G$2:G$2000=$L$30),MATCH(E$2:E$2000,E$2:E$2000,0)),ROW($2:$2000)-1),ROW($2:$2000)-1),ROWS(A$1:$A1))),"")
=IF(J35<>"",SUMPRODUCT((B$2:B$2000=I$30)*(C$2:C$2000+0=J$30)*(D$2:$D$2000=K$30)*(E$2:E$2000=J35)*(G$2:G$2000=L$30)*A$2:A$2000),"")
A pivot table is by far more convenient.
Our results are not aligned: value in A1027 value ($ 370) has been summed twice.
Kind Regards
Stefano
1 Guest(s)