Generating a random comma separated list of product ID's then summing their value?

Solution 1:

If you had Prod ID in column A and price in column B, you could make a random generator of X products (Let's say in E2) like this:

=TEXTJOIN( ", ", 1, INDEX( A2:A11, RANDARRAY(5,1,1,10,1) ) )

where 5 is the number of elements you want and 10 is the count of all products and A2:A11 is the list of product IDs.

Then in let's say E4, you could calculate the sum of the randomly chosen prices with :

=SUM( XLOOKUP( FILTERXML( "<m><s>" & SUBSTITUTE( E2, ", ", "</s><s>" ) & "</s></m>", "//s" ), $A$2:$A$11,$B$2:$B$11,,0) )

where A2:A11 are the prod id's and B2:B11 are the prices.