RockandGrohl
Wellknown Member
 Joined
 Aug 1, 2018
 Messages
 579
 Office Version

 2010
 Platform

 Windows
Hello all.
Let's say I'm trying to count how many products are applicable to papers, the data I have will look like this:
And the products look like this
So what I'm trying to do is match up and count papers and products that have applicable pickups. The trouble is that they are both within a list, so a countif with wildcards will only work one way.
The expected result would be like this:
I guess it's about matching arrays? I have a similar process in another sheet, but it does this:
And from there it tells me how many times either Saltash or Plymouth appear in the pickup list for the product in I2
My question then is there an easy way to do this for my task way above, having a list of papers and their pickups, and wanting to find out how many products can fit in that group, AND THEN AS A WILDCARD, HOW MANY PRODUCTS WITH A CRITERIA, SO I CAN SEE PRODUCTS PER MONTH TOO!
Thanks.
Let's say I'm trying to count how many products are applicable to papers, the data I have will look like this:
Paper Name  Pickups 
Bridlington Press  Bridlington, Scarborough, Filey 
Ross Gazette  RossonWye, Cinderford, Hereford, Newent 
And the products look like this
Product  Pickups 
Isle of Wight Party  Whitby, Scarborough, Pickering, Malton, York, Selby, Pontefract, Doncaster, Worksop, Retford, Bridlington 
Somerset Sunsets  Scarborough, Cinderford, Worcester 
So what I'm trying to do is match up and count papers and products that have applicable pickups. The trouble is that they are both within a list, so a countif with wildcards will only work one way.
The expected result would be like this:
Paper Name  Pickups  Products 
Bridlington Press  Bridlington, Scarborough, Filey  2 
Ross Gazette  RossonWye, Cinderford, Hereford, Newent  0 
I guess it's about matching arrays? I have a similar process in another sheet, but it does this:
 Takes paper pickups
 Separates them out into individual cells
 Determines which pickups are coach pickups
 Discards noncoach pickups
 Applies a variable to each one (Pickup 1, Pickup 2, Pickup 3) etc
 Loads the product list and applies a countif array wildcard formula
Excel Formula:
=COUNT(SEARCH({", Saltash,",", Plymouth,",", Blank,",", Blank,",", Blank,",", Blank,",", Blank,"},", "&I2&","))
And from there it tells me how many times either Saltash or Plymouth appear in the pickup list for the product in I2
My question then is there an easy way to do this for my task way above, having a list of papers and their pickups, and wanting to find out how many products can fit in that group, AND THEN AS A WILDCARD, HOW MANY PRODUCTS WITH A CRITERIA, SO I CAN SEE PRODUCTS PER MONTH TOO!
Thanks.