About pivot table settings

Copper Contributor

Could you give me some advice on the following matters?

Given two tables like this:


--Table 1--
Item A


--Table 2--
Item B     Item C
1             Orange; Apple
4             Banana; Melon; None
2             Strawberry
3             Banana
1             Orange


I want to associate item A and item C and count the number of values in item A.
How should I set the pivot table?

--Expected results--
I want to count the number of each number in item A.

Row label         1 2 3 4
Orange             2 0 0 0
Apple               1 0 0 0
Banana             0 0 1 0

As a side note:
I know how to create a pivot table from multiple tables.
Since item A and item C do not match exactly, I would like to know if there is a way to set them.
*Currently, only values that match item A and item C exactly can be counted.

1 Reply



An alternative without Excel 365 and without a Pivot Table could be SUMPRODUCT.