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
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
Thank you Mynda,
I appreciate the advice, I would however like to figure out the array solution if at all possible.
Best Regards,
Deena
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
Thank you Stefano,
The pivot table suggested by both you and Mynda is more convenient. The array however is brilliant. Sometimes it is the journey in figuring out if something will work, that was my question with this problem. I learned a lot, thank you again.
Deena
Hi,
thanks for your kind feedback.
Greetings from Firenze
