Jan 31 2023 08:25 PM
Could you give me some advice on the following matters?
Given two tables like this:
--Table 1--
Item A
Orange
Apple
Banana
--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.
Feb 01 2023 04:48 AM
=SUMPRODUCT((B$15=$A$9:$A$13)*ISNUMBER(SEARCH($A16,$B$9:$B$13)))
An alternative without Excel 365 and without a Pivot Table could be SUMPRODUCT.