In Worksheet2, create a helper column in E with the formula =A2&D2 in E2, then drag this down the list.
Then in Sheet1, cell B2 put the following formula
drag this along and down to fill the matrix![]()
=COUNTIF(Worksheet2!$E:$E,Worksheet1!$A2&Worksheet1!B$1)
and that should be that
How it works:
Column E creates a single identifier showing both the basket and fruit such as Basket1Orange, Basket1Apple, Basket6Banana etc
The matrix then uses COUNTIF to see how many of each there are in Column E and what to search for by taking the column A value and adding the row 1 value for each item so in cell D10 its looking for A10 (Basket9) and D1 (Banana) giving a search value of "Basket9Banana"
Bookmarks