Counting between worksheets

[joeyga]

Hi folks,

Noob at excel so would appreciate any help. I've attached an example. Attachment 363857

Worksheet 2: Column A lists the basket types
Worksheet 2: Column D lists a type of fruit that should be in the basket

Worksheet 1 should provide a count the number of times a particular fruit has been mapped to a basket in worksheet 2. I've filled out basket 1 in the example.

Hope that makes sense and would appreciate any help.
Thanks,
Joe

[pjwhitfield]

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

Please Login or Register  to view this content.

drag this along and down to fill the matrix

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"

[alansidman]

Welcome to the Forum, unfortunately:

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

Thread Closed.