The comments in Optimisation of food items problem - how do I find the cheapest combination? have been very helpful.
I have managed to find http://web.mit.edu/15.053/www/Excel_Solver.pdf and realised this problem can be partially modelled as a linear programming problem, and tried the Simplex add-in in Excel. It worked perfectly except that I do not know how to specify conditions where certain combinations/quantities of products shouldn't be selected eg.: Say I have 200 products to choose from. (1) Products 5,6,7,8,9 should have max quantity 10 each. (2) Only one of Products 5,6,7,8,9 can be selected (3) Product 5 can be combined only with products from product 15-25
Can these contraints be included in a linear programming problem, and can it be specificed in the Simplex add-in in Excel? If no, how should I solve, or where should I look to solve this? If this is not easy to answer in a single post, references to the appropriate mathematical literature eg. textbooks, lecture notes will suffice.
Let decision variable $x_i$ be the quantity of product $i$ chosen.
For (1), just impose an upper bound of 10 on each of those decision variables: $x_i \le 10$ for $i\in\{5,\dots,9\}$.
For both (2) and (3), you need a binary decision variable $y_i$ to indicate if $x_i > 0$, together with linear "big-M" constraints $x_i \le M_i y_i$, where the numeric parameter $M_i$ is an upper bound on $x_i$. For example, take $M_i=10$ for $i\in\{5,\dots,9\}$.
For (2), impose $\sum_{i=5}^9 y_i \le 1$.
For (3), you want $y_5=1$ to imply $y_i=0$ for $i\not\in\{15,\dots,25\}$. One way to do this is via linear constraints $y_5 + y_i \le 1$ for $i\not\in\{15,\dots,25\}$. A weaker "aggregated" formulation is $$\sum_{i\not\in\{15,\dots,25\}} y_i \le M(1-y_5).$$ Here, $M$ is an upper bound on the left hand side when $y_5=0$, so $M=200-12=188$ is a good choice.